Loop with variable name?

M

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.
 
E

edvwvw via OfficeKB.com

How about PN&i

edvwvw
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.
 
J

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
 
J

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
 
G

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
 
J

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.
 
J

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
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top