Subroutine question and public variables

I am rather new to excel programming and I am trying to figure out some basic things. First, I am not sure how to declare and set a public variable. For example, in my code, I have 2 constants that represent spreadsheets. The code looks like this:

Please Login or Register  to view this content.

I am not sure as to why this doesn't work.

Secondly I am trying to create a subroutine that looks for a column, row, or both that meet certain criteria. This could be a column that matches another field in my userform or has nothing in it. I wrote the following code that should work, but I don't know where to put it or how to call it once it is put there.

Please Login or Register  to view this content.

Any help would be greatly appreciated.

The public variables should be declared at the head of the module, outside
of a sub, but should be set within a sub

Public sh As Worksheet
Public ls As Worksheet

Sub mySub()

Set sh = Worksheets("Assemblies")
Set ls = Worksheets("Assemblylist")

End Sub

Not sure about the other, but I assume that you want the x & y values, so
maybe do something like

myX=0:myY=0
Call lookup(myX,myY)

and then use myX and myY on return



Sub lookup(x,y)
Dim direction As Integer '1 for columns 2 for rows 3 for both
Dim z As Variant

Do While sh.cells(y, x) <> z
If direction = 1 Then x = x + 1
If direction = 2 Then y = y + 1
If direction = 3 Then x = x + 1
If direction = 3 Then y = y + 1
Loop



--
HTH

Bob Phillips

"musictech" <musictech.1ribig_1120248310.2851@excelforum-nospam.com> wrote
in message news:musictech.1ribig_1120248310.2851@excelforum-nospam.com...
>
> I am rather new to excel programming and I am
> trying to figure out some basic things. First, I am not sure how to
> declare and set a public variable. For example, in my code, I have 2
> constants that represent spreadsheets. The code looks like this:
>
>
> Code:
> --------------------
> Public sh As Worksheet
> Public ls As Worksheet
> Set sh = Worksheets("Assemblies")
> Set ls = Worksheets("Assemblylist")
> --------------------
>
>
> I am not sure as to why this doesn't work.
>
> Secondly I am trying to create a subroutine that looks for a column,
> row, or both that meet certain criteria. This could be a column that
> matches another field in my userform or has nothing in it. I wrote the
> following code that should work, but I don't know where to put it or how
> to call it once it is put there.
>
>
> Code:
> --------------------
> Sub lookup()
> Dim direction As Integer '1 for columns 2 for rows 3 for both
> Dim y As Integer 'row integer
> Dim x As Integer 'column integer
> Dim z As String 'Don't really know what to declare this as, could be a
word or a number
>
>
> Do While sh.cells(y, x) <> z
> If direction = 1 Then x = x + 1
> If direction = 2 Then y = y + 1
> If direction = 3 Then x = x + 1
> If direction = 3 Then y = y + 1
> Loop
> --------------------
>
>
> Any help would be greatly appreciated.
>
>
> --
> musictech
> ------------------------------------------------------------------------
> musictech's Profile:
http://www.excelforum.com/member.php...o&userid=24851
> View this thread: http://www.excelforum.com/showthread...hreadid=383979
>