Dynamic Variable name in VBA

[borolo222]

Hi, I have 5 variables
pos1 : 10
pos2 : 19
pos3 : 25
pos4 : 36
pos5 : 42

and i have a loop from 1 to 5 in which i have to change the variable name inside as so to access the content of the variable:

for i=1 to 5
x= cells("pos" & i, 1).value

and so on

so when i combine the name "pos" with a variable part "i", all i get is the name only "pos1". how can i tell vba that the result is a variable and it should use the content and not the name only??

I hope it's clear what i'm trying to explain...

thanks in advance

 

[MickG]

Declare as an Array , then you can do it !!!

Dim i, x
Dim Pos(1 To 5) As Integer
Pos(1) = 10
Pos(2) = 19
Pos(3) = 25
Pos(4) = 36
Pos(5) = 42
For i = 1 To 5
x = Cells(Pos(i), 1).value
Next i

Mick

 

[borolo222]

Sometimes you don't see the simple things....

thanks

 

[Mouli]

Dears,
I’ve got a problem with assigning values to different variables (whom names are nearly the same except the suffix) inside a loop

Dim dada_1 as integer, dada_2 as integer, dada_3 as integer, dada_4 as integer
Dim Uplim as integer, I as integer
Uplim= 4

For I = 1 to Uplim
Dada_& I = I + 1
Next I

The program tells me that the sub or procedure doesn’t exist while I would expect dada_1 = 2, dada_2 = 3, dada_3 = 4, dada_4 = 5

Could you give me some help please?

 

[MickG]

You can't do that!!, But you can do as per the code below.

Dim I As Integer
Dim Dada(1 To 4) As Integer
For I = 1 To 4
    Dada(I) = I + 1
Next I
Range("A1").Resize(UBound(Dada)) = Application.Transpose(Dada)

 

[Nykyta]

Very nice one Mick. Thanks/

Declare as an Array , then you can do it !!!

Dim i, x
Dim Pos(1 To 5) As Integer
Pos(1) = 10
Pos(2) = 19
Pos(3) = 25
Pos(4) = 36
Pos(5) = 42
For i = 1 To 5
x = Cells(Pos(i), 1).value
Next i

Mick

 

[SeniorTom]

New Kid on block, Hope I'm posting correctly. I'm trying to get the value from a variable that I want to amend using a "for next" loop. We have foursomes and I want to check if the individuals have been paired before. This is my first pass at it and I'm new to VBA, so the code is not sophisticated

Sub test()
Application.Workbooks("4-some comparasion.xlsm").Sheets(1).Activate

membercheck = ActiveCell.Value ' This who your checking against
CurrentPartner1 = ActiveCell.Value ' crappy but need to fix later
CurrentPartner2 = ActiveCell.Offset(1, 0).Value
CurrentPartner3 = ActiveCell.Offset(2, 0).Value
CurrentPartner4 = ActiveCell.Offset(3, 0).Value

PreviousPartner1 = Range(WorkingColumn & Foursome - 1).Value ' crappy but need to fix later
PreviousPartner2 = Range(WorkingColumn & Foursome).Value
PreviousPartner3 = Range(WorkingColumn & Foursome + 1).Value
PreviousPartner4 = Range(WorkingColumn & Foursome + 2).Value

' ********* Ok we have today's four and previous four. Now lets see if the played together

Dim I As Integer
Dim Junk As String ' temp name for testing
Dim CurrentPartner(2 To 4) As Integer
For I = 2 To 4
Junk = CurrentPartner(I)
' Junk = "CurrentPartner" & I ... gives me "CurrentPartner2" but not the value of CurrentPartner2
Next I

If Junk/CurrentPartner2 = PreviousPartner1 Then ' has he played with this guy ?
MsgBox "Matched"
Else
If Junk/CurrentPartner2 = PreviousPartner2 Then
ActiveCell.Offset(0, 1).Value = 1
MsgBox "Matched"
Else
If Junk/CurrentPartner2 = PreviousPartner3 Then
ActiveCell.Offset(1, 1).Value = 1
MsgBox "Matched"