Changing a variable which is to be specified through a loop

[Wesslan]

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]

VBA doesn't let you do this kind of thing with variables.

 

[Wesslan]

Alright, then at least I know. Arrays it is!