Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Multiple Variables' Declaration In Loops
Any idea how to declare multiple variables depending upon the times looping
shall be carried on. An idea may be grasped by my unfit CoDe as below: Sub test() Dim Counter As Long Counter = Application.InputBox("How Many Ranges?", , , , , , , 1) For x = 1 To Counter Dim Range & x As range set Range & x = application.InputBox("Select Range " & x) Next For Z = 1 To Counter msgbox "Range "& Z " comprised of " & (Range & Z).address Next End Sub Please help me out by rectifying the above code, or designing a similar code or referring to a site, for which I shall be grateful. Thanx in advance. -- Best Regards, Faraz |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Multiple Variables' Declaration In Loops
You should be using an (dynamic) array. You would declare it like this...
Dim MyRange() As Range then ReDim it to the correct size after you know how big to make it (that is, after the Counter variable is set). Then you use the loop counter as the index for the array. Something like this is what I think you are looking for... Sub test() Dim X As Long, Counter As Long Dim MyRange() As Range Counter = Application.InputBox("How Many Ranges?", , , , , , , 1) ReDim MyRange(1 To Counter) For X = 1 To Counter Set MyRange(X) = Application.InputBox("Select Range(" & _ X & ")", , , , , , , 8) Next For X = 1 To Counter MsgBox "MyRange(" & X & ") comprised of " & MyRange(X).Address Next End Sub -- Rick (MVP - Excel) "Faraz A. Qureshi" wrote in message ... Any idea how to declare multiple variables depending upon the times looping shall be carried on. An idea may be grasped by my unfit CoDe as below: Sub test() Dim Counter As Long Counter = Application.InputBox("How Many Ranges?", , , , , , , 1) For x = 1 To Counter Dim Range & x As range set Range & x = application.InputBox("Select Range " & x) Next For Z = 1 To Counter msgbox "Range "& Z " comprised of " & (Range & Z).address Next End Sub Please help me out by rectifying the above code, or designing a similar code or referring to a site, for which I shall be grateful. Thanx in advance. -- Best Regards, Faraz |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Multiple Variables' Declaration In Loops
Sub test() Dim RangesArray() Dim Counter As Long Counter = Application.InputBox("How Many Ranges?", , , , , , , 1) ReDim RangesArray(Counter) For x = 1 To Counter Set RangesArray(x) = Application.InputBox("Select Range", Type:=8) Next x For Z = 1 To Counter MsgBox "Range " & Z & " comprised of " & RangesArray(Z).Address Next End Sub -- p45cal *p45cal* ------------------------------------------------------------------------ p45cal's Profile: http://www.thecodecage.com/forumz/member.php?userid=558 View this thread: http://www.thecodecage.com/forumz/sh...d.php?t=133672 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Multiple Variables' Declaration In Loops
sub somethinglikethis()
dim l as long dim x as long dim aStr() as string l = Application.InputBox("How Many Ranges?", , , , , , , 1) redim preserve aStr(l) for x = 1 to l aStr(x) = application.InputBox("Select Range " & x) next x for x = 1 to l msgbox "Range " & x & " adress is " & astr(x) next x end sub "Faraz A. Qureshi" wrote: Any idea how to declare multiple variables depending upon the times looping shall be carried on. An idea may be grasped by my unfit CoDe as below: Sub test() Dim Counter As Long Counter = Application.InputBox("How Many Ranges?", , , , , , , 1) For x = 1 To Counter Dim Range & x As range set Range & x = application.InputBox("Select Range " & x) Next For Z = 1 To Counter msgbox "Range "& Z " comprised of " & (Range & Z).address Next End Sub Please help me out by rectifying the above code, or designing a similar code or referring to a site, for which I shall be grateful. Thanx in advance. -- Best Regards, Faraz |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Multiple Variables' Declaration In Loops
Concept of ReDIM was XClent 4 sure pal!
Thanx again. -- Best Regards, Faraz "Rick Rothstein" wrote: You should be using an (dynamic) array. You would declare it like this... Dim MyRange() As Range then ReDim it to the correct size after you know how big to make it (that is, after the Counter variable is set). Then you use the loop counter as the index for the array. Something like this is what I think you are looking for... Sub test() Dim X As Long, Counter As Long Dim MyRange() As Range Counter = Application.InputBox("How Many Ranges?", , , , , , , 1) ReDim MyRange(1 To Counter) For X = 1 To Counter Set MyRange(X) = Application.InputBox("Select Range(" & _ X & ")", , , , , , , 8) Next For X = 1 To Counter MsgBox "MyRange(" & X & ") comprised of " & MyRange(X).Address Next End Sub -- Rick (MVP - Excel) "Faraz A. Qureshi" wrote in message ... Any idea how to declare multiple variables depending upon the times looping shall be carried on. An idea may be grasped by my unfit CoDe as below: Sub test() Dim Counter As Long Counter = Application.InputBox("How Many Ranges?", , , , , , , 1) For x = 1 To Counter Dim Range & x As range set Range & x = application.InputBox("Select Range " & x) Next For Z = 1 To Counter msgbox "Range "& Z " comprised of " & (Range & Z).address Next End Sub Please help me out by rectifying the above code, or designing a similar code or referring to a site, for which I shall be grateful. Thanx in advance. -- Best Regards, Faraz |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Multiple Variables' Declaration In Loops
Another XClent 1
-- Best Regards, Faraz "p45cal" wrote: Sub test() Dim RangesArray() Dim Counter As Long Counter = Application.InputBox("How Many Ranges?", , , , , , , 1) ReDim RangesArray(Counter) For x = 1 To Counter Set RangesArray(x) = Application.InputBox("Select Range", Type:=8) Next x For Z = 1 To Counter MsgBox "Range " & Z & " comprised of " & RangesArray(Z).Address Next End Sub -- p45cal *p45cal* ------------------------------------------------------------------------ p45cal's Profile: http://www.thecodecage.com/forumz/member.php?userid=558 View this thread: http://www.thecodecage.com/forumz/sh...d.php?t=133672 |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Multiple Variables' Declaration In Loops
Sorry Sam,
But the result was on Range 1/2/3/4 address is. -- Best Regards, Faraz "Sam Wilson" wrote: sub somethinglikethis() dim l as long dim x as long dim aStr() as string l = Application.InputBox("How Many Ranges?", , , , , , , 1) redim preserve aStr(l) for x = 1 to l aStr(x) = application.InputBox("Select Range " & x) next x for x = 1 to l msgbox "Range " & x & " adress is " & astr(x) next x end sub "Faraz A. Qureshi" wrote: Any idea how to declare multiple variables depending upon the times looping shall be carried on. An idea may be grasped by my unfit CoDe as below: Sub test() Dim Counter As Long Counter = Application.InputBox("How Many Ranges?", , , , , , , 1) For x = 1 To Counter Dim Range & x As range set Range & x = application.InputBox("Select Range " & x) Next For Z = 1 To Counter msgbox "Range "& Z " comprised of " & (Range & Z).address Next End Sub Please help me out by rectifying the above code, or designing a similar code or referring to a site, for which I shall be grateful. Thanx in advance. -- Best Regards, Faraz |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Passing Variables within For loops to other modules | Excel Programming | |||
Help please! Loops? For Each? Variables? | Excel Programming | |||
Multiple loops one result | Excel Programming | |||
Correct declaration of variables | Excel Programming | |||
Correct declaration of variables | Excel Programming |