Variable Variable??
Posted by RoB on August 21, 2001 10:54 AM
I am trying to put a loop into a macro which pretty much everyone on this board has helped me with (thanks to you all). I have created a piece of code to do what I want it to, but I know the syntax is incorrect for VB (as I dont know it) and also, I don't know if my method will work... heres the code: (This isnt the whole macro, only the necessary parts)
Dim NumberConditions as Integer
Dim Counter as Integer
Dim FilterCriteria
NumberConditions = InputBox("Enter # of conditions to filter")
For Counter = 1 to NumberConditions
FilterCriteria(Counter) = InputBox("Enter Criteria Counter")
Next
Selection.AutoFilter field:=ColumnFilterSelect, Criteria1:=FilterCriteria1this part needs to be modified to contain how ever many criteria the user selects, ie: if the user says 3 criteria, it would read: Criteria1:=FilterCriteria1, Criteria2:=FilterCriteria2, Criteria3:=FilterCriteria3
What I want to be able to do is define a variable using a varible. for example, the FilterCriteria(Counter). so when this is on the first counter, it will read FilterCriteria1= whatever user inputs... Is this possible, or am I way off track and theres a WAY simpler way? I think the defining is going to be a problem because the variables say, FilterCriteria2 would not be defined. Also, how would I in the last part get the Criteria2:=FilterCriteria2. Woudl this require another loop?
Thanks for any input :)
Posted by Robb on August 22, 2001 5:28 AM
Rob
I think a better way of doing something like you want is to use Select Case to set the FilterCriteria variables. Excel does not like more than 2 criteria, so I have limited it to that. I suppose you could do multiple filters, but I don't really think it would be too successful.
Do you really nedd so many filter criteria? Anyway, try this code:
Dim NumberConditions As Integer
Dim Counter, myF
Dim FilterCriteria
NumberConditions = InputBox("Enter # of conditions to filter")
For Counter = 1 To NumberConditions
FilterCriteria = InputBox("Enter Criteria " & Counter)
Select Case Counter
Case 1
Filtercriteria1 = FilterCriteria
Case 2
filtercriteria2 = FilterCriteria
Case Else
MsgBox "Too many criteria"
End Select
If Counter = NumberConditions Then Exit For
Next Counter
myF = Counter
Select Case myF
Case 1
Worksheets("Whatever").Range("Whatever").AutoFilter _
field:=5, Criteria1:=Filtercriteria1
Case 2
Worksheets("Whatever").Range("Whatever").AutoFilter _
field:=5, Criteria1:=Filtercriteria1, _
Operator:=xlOr, _
Criteria2:=filtercriteria2
Case Else
MsgBox "Filter error"
End Select
Hope it helps.
Regards
