In VBScript you can do something like that, Wries. I don't know of a way to do it directly in VBA. But depending on what you need, you may not have to. Do you just need to set three (or more) values in a loop? If so, there are several really good ways.
1) Use an "array". An array is a series of variables with a name plus a number, any number you like from
x to
y. In your example it would work like this:
Code:
Dim Rank(1 To 3)
For i = 1 to 3
Rank(i) = 'cell reference value
Next i
For "cell reference value" you plug in whatever you like.
2) Use a collection. A collection is like an array but more flexible. For one thing you can
name the items in a collection, as well as number them. For another, items of a collection can be objects, not just scalar values (numbers or character strings, I mean). And you don't have to decide ahead of time how many you want. With an array you have to use the Dim statement to give the array a predetermined lower and upper bound; but a collection doesn't have to know ahead of time how many items it'll hold. Like this:
Code:
Set Coll = New Collection
For i = 1 to 3
Coll.Add ActiveWorksheet.Rows(i + 1)
Next i
This stores in the collection three entire
rows (2, 3 and 4) of the currently active worksheet.
3) Use a worksheet. If for some reason you don't like either of the above two choices, you can store the data in a spare worksheet, if you have one handy.
Code:
Set os = ThisWorkbook.Worksheets("Sheet3")
For i = 1 to 3
os.Cells(i, 5).Value = "some value"
Next i
This isn't as good, in most cases, because making something happen in the worksheet is slower than doing it in an array or collection. Three items doesn't make any difference, but you'll definitely notice if you try it with a thousand. Still, it's an option.
Are any of those a help?