Loop with variable name?

[Mike]

Hi. I have 12 variables that are names PN1, PN2, .... PN12

I would like to have a "For - Next" loop that would execute for i = 1 to 12
where "i" would change in the variable name. I cannot figure out how to
refer to the variable:

For i = 1 to 12

x = PNi + 5

Next i

Any help would be appreciated ...

Thanks,
Mike.

 

[Jim Thomlinson]

You can not create varaible references on the fly. You are better off with an
array.

Dim lng As Long
Dim PN(12) As String

PN(0) = "A"
PN(1) = "B"
PN(2) = "C"
PN(3) = "D"

For lng = 0 To 11
MsgBox PN(lng)
Next lng

 

[Jacob Skaria]

Use an array.

Dim arrPI
Dim intTemp

arrPI =("a","b","c","d")
For intTemp = 0 to Ubound(arrPI)
Msgbox arrPI(intTemp)
Next


If you want to assign variables at run time declare

Dim arrPI(10) as Variant
arrPI(0) = "Something0"
arrPI(1) = "Something1"
arrPI(2) = "Something2"
arrPI(3) = "Something2"
..
upto (You can store a max of 11 variables in this array)
arrPI(10) = "Something10"






If this post helps click Yes

 

[Gary''s Student]

Use an array:

Option Base 1
Sub Mike()
Dim PN(12) As Integer
For i = 1 To 12
PN(i) = i
Next
End Sub

 

[JLatham]

Hopefully it isn't too late for you to make this change:

Instead of using variables named PN1, PN2 ... PN12, use an array, which you
could call PN. Set it up this way:

Dim PN(1 to 12) As type
where type refers to the type of values to go into it, as Integer (whole
numbers), Long (great big whole numbers), single, double, etc.

Then instead of statements like PN1 = 4, you refer to them as PN(1) = 4,
PN(9) = 6, etc.

So in your loop, it all becomes very easy:

For i = 1 to 12
x = PN(i) + 5
Next i

Of course, in your example, x is always going to end up being the value in
PN(12) + 5, but I presume it's a trivial example.

Better even than using For i = 1 to 12, do this:

For i = LBound(PN) To UBound(PN)
x = PN(i) + 5
Next i

LBound and UBound refer to the lower and upper boundary of the array
referenced. So if you ever change the number of elements in the array, your
loop automatically adjusts to the new size.

Hope this helps some.

 

[Jacob Skaria]

A small correction

Use an array.

Dim arrPI
Dim intTemp

arrPI = Array("a","b","c","d")
For intTemp = 0 to Ubound(arrPI)
Msgbox arrPI(intTemp)
Next


If you want to assign variables at run time declare

Dim arrPI(10) as Variant
arrPI(0) = "Something0"
arrPI(1) = "Something1"
arrPI(2) = "Something2"
arrPI(3) = "Something2"
..
upto (You can store a max of 11 variables in this array)
arrPI(10) = "Something10"


If this post helps click Yes