Reputation: 11
I was trying to get a string to work with a code where I automate multiple tabs to be filtered by the same criteria, for a sheet that gets the subtotal out of other sheets.
So, I did some If
and Else
s to check for some criteria and try to make the filtering after this analysis, instead of having to do multiple AutoFilter
s withing multiple If
s.
So, the part bellow works just fine, it is making the string (subStr
) accordingly
If subBacia = "TODOS" Then
subStr = """<>"""
Else
subStr = """=" & subBacia & QUOTE & " , Operator:=xlOr, Criteria2:=" & QUOTE & "=TOTAIS" & QUOTE & ""
End If
But this part, that I am avoiding to do multiple if
s for, is not working:
ActiveSheet.Range("$A$12:$EX$2025").AutoFilter Field:=4, Criteria1:=subStr
My idea was that it would end up as the construction bellow, for example, with multiple criteria:
ActiveSheet.Range("$A$12:$EX$2025").AutoFilter Field:=4, Criteria1:="=AN6b" _
, Operator:=xlOr, Criteria2:="=TOTAIS"`
So, what am I doing wrong, or what should I do that I am not aware of? Edit: Here goes the full code
Sub Filter()
Dim subBacia, encarregado As String
Dim bm As String
Dim subStr, encStr, bmStr As String
'Application.Calculation = xlCalculationManual
'Application.ScreenUpdating = False
Sheets("Resumo").Select
encarregado = Range("T3")
subBacia = Range("T5")
bm = Range("T4")
Const QUOTE = """"
On Error Resume Next
If encarregado = "TODOS" And bm = "TODOS" And subBacia = "TODOS" Then
Sheets("Dem. Rede").Select
ActiveSheet.ShowAllData
Sheets("Dem. Interceptor").Select
ActiveSheet.ShowAllData
Sheets("Dem.Ramal").Select
ActiveSheet.ShowAllData
Else
If encarregado = "TODOS" Then
encStr = """<>"""
Else
encStr = """=" & encarregado & QUOTE & " , Operator:=xlOr, Criteria2:=" & QUOTE & "=TOTAIS" & QUOTE & ""
End If
If bm = "TODOS" Then
bmStr = """<>"""
Else
bmStr = """=" & bm & QUOTE & " , Operator:=xlOr, Criteria2:=" & QUOTE & "=TOTAIS" & QUOTE & ""
End If
If subBacia = "TODOS" Then
subStr = """<>"""
Else
subStr = """=" & subBacia & QUOTE & " , Operator:=xlOr, Criteria2:=" & QUOTE & "=TOTAIS" & QUOTE & ""
End If
Debug.Print encStr
Debug.Print bmStr
Debug.Print subStr
Sheets("Dem. Rede").Select
ActiveSheet.Range("$A$12:$EX$2025").AutoFilter Field:=2, Criteria1:=bmStr
ActiveSheet.Range("$A$12:$EX$2025").AutoFilter Field:=3, Criteria1:=encStr
ActiveSheet.Range("$A$12:$EX$2025").AutoFilter Field:=4, Criteria1:=subStr
Sheets("Dem. Interceptor").Select
ActiveSheet.Range("$A$12:$EM$137").AutoFilter Field:=2, Criteria1:=bmStr
ActiveSheet.Range("$A$12:$EM$137").AutoFilter Field:=4, Criteria1:=encStr
ActiveSheet.Range("$A$12:$EM$137").AutoFilter Field:=3, Criteria1:=subStr
Sheets("Dem.Ramal").Select
ActiveSheet.Range("$B$11:$Z$1214").AutoFilter Field:=3, Criteria1:=bmStr
ActiveSheet.Range("$B$11:$Z$1214").AutoFilter Field:=2, Criteria1:=encStr
ActiveSheet.Range("$B$11:$Z$1214").AutoFilter Field:=1, Criteria1:=subStr
Sheets("Cadastro Ramal").Select
ActiveSheet.Range("$A$9:$K$841").AutoFilter Field:=2, Criteria1:=bmStr
End If
'Application.Calculation = xlCalculationAutomatic
'Application.ScreenUpdating = True
End Sub
Edit: BTW the Debug.Print
is returning for example: "=9" , Operator:=xlOr, Criteria2:="=TOTAIS"
or simply "<>"
And it is exactly the way it should, but, it seems I can't concatenate the string in the code the way I hoped for.
Upvotes: 1
Views: 170
Reputation: 78210
You cannot construct pieces of VBA code on the fly and use them to complement incomplete statements you have hardcoded. I don't think that ever worked anywhere. You can dynamically create a complete sub
and execute it though, but I do not recommend that.
However you don't need dynamic code generation in your case anyway. You want to predetermine values for the parameters, so do that explicitly. The trick is that you will need to use the Missing
special value explicitly to pass to parameters you don't want:
Private Type CriteriaTuple
Criteria1 As Variant
Operator As Variant
Criteria2 As Variant
End Type
Private Function MissingValue(Optional ByVal DoNotPassAnything As Variant) As Variant
If Not IsMissing(DoNotPassAnything) Then Err.Raise 5, , "I said do not pass anything"
MissingValue = DoNotPassAnything
End Function
Sub Filter()
....
dim subStr as CriteriaTuple
if ... then
subStr.Criteria1 = """<>"""
subStr.Operator = MissingValue
subStr.Criteria2 = MissingValue
else
subStr.Criteria1 = """<>"""
subStr.Operator = xlOr
subStr.Criteria2 = """=TOTAIS"""
end if
...
ActiveSheet.Range("$A$12:$EX$2025").AutoFilter Field:=4, Criteria1:=subStr.Criteria1, Operator:=subStr.Operator, Criteria2:=subStr.Criteria2
End Sub
Upvotes: 1
Reputation: 46
Unless you can show otherwise, I don't think you can make VBA interpret your string as a parameter list. I haven't understood what you're trying to save by this method.
Upvotes: 0