Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
In A macro create a string of variables as needed
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? |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
In A macro create a string of variables as needed
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. "D. Jones" wrote: 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? |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
In A macro create a string of variables as needed
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" wrote: 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? |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
In A macro create a string of variables as needed
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" wrote: 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" wrote: 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? |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
In A macro create a string of variables as needed
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. "D. Jones" wrote: 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" wrote: 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" wrote: 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? |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
In A macro create a string of variables as needed
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" wrote: 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" wrote: 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" wrote: 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? |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
In A macro create a string of variables as needed
You're good thanks a lot. I would have never figured this one out.
"JLGWhiz" wrote: 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" wrote: 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" wrote: 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" wrote: 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? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
String variables | New Users to Excel | |||
Loosing variables help needed | Excel Programming | |||
Clarification needed about Scope Of Variables | Excel Programming | |||
VERY Basic Define Variables Help Needed | Excel Programming | |||
String Variables | Excel Programming |