Setting Multiple Variables with a loop

[jlclyde]

Is this possible to set mutiple variables with a loop? I want to go
through a range and for each found item I want the variable set. Here
is what I was thinking, but obviously it does nto work.

Dim Con1 as Range, Con2 as Range, Con3 as Range etc...
For X = 1 to 6
if not Range("A1:A50").find(what:=X & "* Convert", Lookin:=
Xlvalues) is nothing then
Con & X = Range("A1:A50").find(what:=X & "* Convert",
Lookin:= Xlvalues)
End if
Next X

Any help woudl be greatly appreciated,
Jay

 

[Dave Peterson]

I'm not quite sure what you're doing, but maybe something like:

dim FoundCell as range
dim Con(1 to 6) as variant
dim x as long

for x = lbound(con) to ubound(con)
'I'd specify all those .find parms. If you don't, your code
'will inherit whatever the last find used (either by the user
'or by some other code.
set foundcell = range("a1:A10").find(what:=x & "* convert", _
lookin:=xlvalues)

if foundcell is nothing then
con(x) = "not found" 'or something else???
else
con(x) = foundcell.value
end if
next x

'just to prove that we found it
for x = lbound(con) to ubound(con)
msgbox con(x)
next x

 

[Dave Peterson]

ps. If you wanted to keep track of the foundcells as ranges:

dim FoundCell as range
dim Con(1 to 6) as range
dim x as long

for x = lbound(con) to ubound(con)
'I'd specify all those .find parms. If you don't, your code
'will inherit whatever the last find used (either by the user
'or by some other code.
set foundcell = range("a1:A10").find(what:=x & "* convert", _
lookin:=xlvalues)

if foundcell is nothing then
set con(x) = nothing
else
set con(x) = foundcell
end if
next x

'just to prove that we found it
for x = lbound(con) to ubound(con)
if con(x) is nothing then
'not there
else
msgbox x & vblf & con(x).address
end if
next x

 

[jlclyde]

ps.  If you wanted to keep track of the foundcells as ranges:

dim FoundCell as range
dim Con(1 to 6) as range
dim x as long

for x = lbound(con) to ubound(con)
  'I'd specify all those .find parms.  If you don't, your code
  'will inherit whatever the last find used (either by the user
  'or by some other code.
  set foundcell = range("a1:A10").find(what:=x & "* convert", _
                       lookin:=xlvalues)

  if foundcell is nothing then
      set con(x) = nothing
  else
      set con(x) = foundcell
  end if
next x

'just to prove that we found it
for x = lbound(con) to ubound(con)
   if con(x) is nothing then
      'not there
   else
      msgbox x & vblf & con(x).address
   end if
next x












--

Dave Peterson- Hide quoted text -

- Show quoted text -

Dave,
This is exactly what I was looking for. I knew there had to be away.
I am currently setting 6 different ranges based on where it is found 6
different times. Thanks for all of your help.

Jay

 

[jlclyde]

Dave,
I shoudl have also asked on how to set text boxes with the same
numbering system. I am trying to find 1 to 6 for convert runs, which
you have done so very nicely. I also need to set the values of text
boxes on a userform. This is all happening at the intialize phase.

Thanks,
Jay

 

[Dave Peterson]

If you name your user forms nicely, you can use those variables.

Is this in the UserForm_Initialize routine?

I'm guessing yes...

'just to prove that we found it
for x = lbound(con) to ubound(con)
me.controls("textbox" & x).value = con(x)
next x

(this used the first suggestion--not treating con() as a range).

 

[jlclyde]

If you name your user forms nicely, you can use those variables.

Is this in the UserForm_Initialize routine?

I'm guessing yes...

'just to prove that we found it
for x = lbound(con) to ubound(con)
   me.controls("textbox" & x).value = con(x)
next x

(this used the first suggestion--not treating con() as a range).

Dave,
This is incredible stuff. Thank you for taking the time to respond.
This is exactly what I need and will help me out in many other
applications and forms that I have floating around.

Thanks,
Jay