In A macro create a string of variables as needed

[D. Jones]

Is it possible to program a variable (new name) for each occurance required
during a loop statement.

For example can we make a variabe = to a cell value and then on the next
pass change teh varialbe by the count ot save another cell value to this new
variable name. ie. "AMT1" for the first scan and cell value that matches
criteria programed and tehn on the next loop the varialbe can be named "AMT2"
and so on and so on?

 

[JLGWhiz]

In the psuedo code below, myVar will change on each iteration of the For loop.
It will get its value from column B and post the values on the same row in
cells to the right.

Sub chngVarVal()
For i = 1 To 5
For Each c In Sheets(1).Range("A1:A5")
If c <> ABC Then
myVar = Cells(i, 2)
c.Offset(0, 5) = myVar
End If
End Sub

To test it. Leave Col A blank and put any varying data in B1:B5. then run
the code.

 

[JLGWhiz]

I know better than that. Use this one.

Sub chngVarVal()
For i = 1 To 5
If Cells(i, 1) <> ABC Then
myVar = Cells(i, 2)
c.Offset(0, 5) = myVar
End If
Next
End Sub

 

[D. Jones]

Thank you for the response but I haven't defined my question clearly. I want
to store many different values each with a different (unique) variable anme
so that when I write the code to paste the values into cells I have all the
values stored. I write from one sheet to another many times and do not want
to switch between the sheets after each loop. So i would have values for
myvar1, myvar2, myvar3 as many times as I have satisfied my do until and if
statments within the "loop" statement. The number of variables will change
depsneding on the matcheds found.

 

[JLGWhiz]

I know this can be done. Probably using an array. I'll work on it tomorrow.
right now I can hardly hold my eyes open.

 

[JLGWhiz]

I gave it one more shot before knocking off for the night. Here is one that
uses an array and I actually tested this one. It does capture the different
values and stores them in an array with a different variable for each value.

Sub chnVarVal()
Dim myVar(5) As Variant
For i = 1 To 5
If Cells(i, 1) < 1 Then
myVar(i) = Cells(i, 2)
End If
Next
MsgBox myVar(1) & ", " & myVar(2) & ", " & myVar(3) & ", " & _
myVar(4) & ", " & myVar(5)
End Sub

 

[D. Jones]

You're good thanks a lot. I would have never figured this one out.