If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Setting Multiple Variables with a loop
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 |
#2
|
|||
|
|||
Setting Multiple Variables with a loop
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 jlclyde wrote: 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 |
#3
|
|||
|
|||
Setting Multiple Variables with a loop
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 wrote: 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 jlclyde wrote: 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 -- Dave Peterson |
#4
|
|||
|
|||
Setting Multiple Variables with a loop
On Nov 11, 11:13*am, Dave Peterson wrote:
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 wrote: 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 jlclyde wrote: 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 -- 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 |
#5
|
|||
|
|||
Setting Multiple Variables with a loop
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 |
#6
|
|||
|
|||
Setting Multiple Variables with a loop
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 wrote: 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 |
#7
|
|||
|
|||
Setting Multiple Variables with a loop
On Nov 11, 1:30*pm, Dave Peterson wrote:
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 wrote: 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 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 |
Thread Tools | |
Display Modes | |
|
|