Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Changing a variable which is to be specified through a loop
Dear programmers out there. I have come across a problem which I am
certain should be possible to solve. I have tried searching for it on the discussion forum, but without any good results. What I am struggling with is that I want the sc & i variable to increase each time the loop is run. So in the first case the variable to be specified would be sc1, the second time sc2 and so forth.. The code which is most interesting and struggling is thus: i = 0 Do While Ans = 6 i = i + 1 sc& i = InputBox("What is the search criteria?", "Search criteria") range_sc& i = InputBox("Which is the search range?", "Search Range") Ans = MsgBox("Do you want to add another search variable?", vbYesNo, "Add another search variable?") Loop However, Excel thinks that sc& i is a function or a sub, not a variable. Do you know a solution to this? I know that I can create an Array and plug in the numbers there, but I think this could be a reoccuring dilemma and then it is easier for clients to see if I keep it to variables.. Best reagards, Peder Should anyone be interested the entire code is now: The entire code: Sub MultipleSearchCriteria() Dim range_val, sc1, sc2, sc3, sc4, Ans As String Dim range_sc1, range_sc2, range_sc3, range_sc4 As String Dim NumberofSearchCriteria As Double Dim i As Double range_val = InputBox("Which is the range containing the values?", "Column with values") Ans = 6 i = 0 Do While Ans = 6 i = i + 1 sc& i = InputBox("What is the search criteria?", "Search criteria") range_sc& i = InputBox("Which is the search range?", "Search Range") Ans = MsgBox("Do you want to add another search variable?", vbYesNo, "Add another search variable?") Loop Select Case i Case 1 ActiveCell.FormulaArray = "=INDEX(" & range_val & ",MATCH(1,(" & sc1 & "=" & range_sc1 & "),0))" Case 2 ActiveCell.FormulaArray = "=INDEX(" & range_val & ",MATCH(1,(" & sc1 & "=" & range_sc1 & ")*(" _ & sc2 & "=" & range_sc2 & "),0))" Case 3 ActiveCell.FormulaArray = "=INDEX(" & range_val & ",MATCH(1,(" & sc1 & "=" & range_sc1 & ")*(" _ & sc2 & "=" & range_sc2 & ")*(" & sc3 & "=" & range_sc3 & "),0))" Case 4 ActiveCell.FormulaArray = "=INDEX(" & range_val & ",MATCH(1,(" & sc1 & "=" & range_sc1 & ")*(" _ & sc2 & "=" & range_sc2 & ")*(" & sc3 & "=" & range_sc3 & ")*(" & sc4 & "=" & range_sc4 & "),0))" End Sub |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Changing a variable which is to be specified through a loop
VBA doesn't let you do this kind of thing with variables.
Wesslan wrote: Dear programmers out there. I have come across a problem which I am certain should be possible to solve. I have tried searching for it on the discussion forum, but without any good results. What I am struggling with is that I want the sc & i variable to increase each time the loop is run. So in the first case the variable to be specified would be sc1, the second time sc2 and so forth.. The code which is most interesting and struggling is thus: i = 0 Do While Ans = 6 i = i + 1 sc& i = InputBox("What is the search criteria?", "Search criteria") range_sc& i = InputBox("Which is the search range?", "Search Range") Ans = MsgBox("Do you want to add another search variable?", vbYesNo, "Add another search variable?") Loop However, Excel thinks that sc& i is a function or a sub, not a variable. Do you know a solution to this? I know that I can create an Array and plug in the numbers there, but I think this could be a reoccuring dilemma and then it is easier for clients to see if I keep it to variables.. Best reagards, Peder Should anyone be interested the entire code is now: The entire code: Sub MultipleSearchCriteria() Dim range_val, sc1, sc2, sc3, sc4, Ans As String Dim range_sc1, range_sc2, range_sc3, range_sc4 As String Dim NumberofSearchCriteria As Double Dim i As Double range_val = InputBox("Which is the range containing the values?", "Column with values") Ans = 6 i = 0 Do While Ans = 6 i = i + 1 sc& i = InputBox("What is the search criteria?", "Search criteria") range_sc& i = InputBox("Which is the search range?", "Search Range") Ans = MsgBox("Do you want to add another search variable?", vbYesNo, "Add another search variable?") Loop Select Case i Case 1 ActiveCell.FormulaArray = "=INDEX(" & range_val & ",MATCH(1,(" & sc1 & "=" & range_sc1 & "),0))" Case 2 ActiveCell.FormulaArray = "=INDEX(" & range_val & ",MATCH(1,(" & sc1 & "=" & range_sc1 & ")*(" _ & sc2 & "=" & range_sc2 & "),0))" Case 3 ActiveCell.FormulaArray = "=INDEX(" & range_val & ",MATCH(1,(" & sc1 & "=" & range_sc1 & ")*(" _ & sc2 & "=" & range_sc2 & ")*(" & sc3 & "=" & range_sc3 & "),0))" Case 4 ActiveCell.FormulaArray = "=INDEX(" & range_val & ",MATCH(1,(" & sc1 & "=" & range_sc1 & ")*(" _ & sc2 & "=" & range_sc2 & ")*(" & sc3 & "=" & range_sc3 & ")*(" & sc4 & "=" & range_sc4 & "),0))" End Sub -- Dave Peterson |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Changing a variable which is to be specified through a loop
Alright, then at least I know. Arrays it is!
|
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Loop with variable name? | Excel Discussion (Misc queries) | |||
LAST value of loop to be a variable | Excel Programming | |||
loop for-next with variable end | Excel Programming | |||
For Each ... Next loop - need to reference the loop variable | Excel Programming | |||
Loop with variable | Excel Programming |