Dynamic Variable name in VBA

borolo222

New Member
Joined
Aug 18, 2009
Messages
42
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...:eek:

thanks in advance
 

Excel Facts

Save Often
If you start asking yourself if now is a good time to save your Excel workbook, the answer is Yes

MickG

MrExcel MVP
Joined
Jan 9, 2008
Messages
14,841
Declare as an Array , then you can do it !!!
Code:
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
 

Mouli

New Member
Joined
Feb 6, 2015
Messages
1
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

MrExcel MVP
Joined
Jan 9, 2008
Messages
14,841

ADVERTISEMENT

You can't do that!!, But you can do as per the code below.
Code:
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

New Member
Joined
Aug 14, 2014
Messages
43
Very nice one Mick. Thanks/

Declare as an Array , then you can do it !!!
Code:
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

Board Regular
Joined
Jun 5, 2017
Messages
89
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"
 

Forum statistics

Threads
1,147,635
Messages
5,742,245
Members
423,717
Latest member
rubthenut

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top