Autofilter keywords in string variable

[davidem]

Greetings,

I've searched high and low and can't find any help for what I'm trying to do. I have been successful in using variables in the AutoFilter Field:= and Criteria1:= keywords...but in my situation I am attempting to push the variables further into the AutoFilter statement.

I have a spreadsheet with multiple sheets that show the client with multiple views of a large table. From the sheet that the user is looking at (analyzing) it's just a summary of the rows of data - I wanted to provide a view of the data based on the table the client is looking at (show them the data behind the summary). I'm not a strong VBA coder but am able to bash code together to make things work (thanks in much to the MrExcel community).

So what I want to do is allow the client to execute the macro via a button and send a string to my AutoFilter macro (successful in passing the parm) and use that STRING AFTER the AutoFilter command. The parm passed would be hard coded for each cell in the table based on "countif" function for each button on the sheet. So if the user click on the button/image I would pass it a string such as "Field:=8, Criteria1:=>90,Operator:=x1And, Criteria2:<100". This string would be assigned to a variable (say AutoStr1) in the macro (I can pass the variable from the sheet to the macro). The string is valid because if I hardcode that into the AutoFilter statement it works. The macro should then take the string and do ".Range("A1:AE1") .AutoFilter AutoStr1. I'm trying this method because AutoStr1 could be a simple 2 criteria statement or much more complex statement with multiple expressions based on the summary table view.

When I invoke the marco from the sheet it fails with "Run-time error '1004': AutoFilter method Range class failed" message. But if I take that exact string and hardcode it into the AutoFilter statement it runs OK...so I'm not sure how/if I can replace all for the statement after the AutoFilter with a string. I've added characters like "&", ".", etc. in a futile attempt at getting the string to be recognized as a valid continuation of the AutoFilter command w/o any luck.

Hopefully I've made clear what I'm attempting to accomlish.

Thanks,
David

 

[davidem]

Hopefully this example will help.

Severity Level SEV1 SEV2
Days Late 100+ 90 100+ 90

Examples:
If user clicks on the cell (image) that would be over the Days Late "90" cell and under the Sev 1 column it would pass the following to the macro:
.AutoFilter Field:=8, Criteria1:="Severity 1" .Range("A1:AE1").AutoFilter Field:=5, Criteria1:=>90,Operator:=xlAnd, Criteria2:<100

If user clicks on the cell (image) that would be over the Days Late "100+" cell and under the Sev 1 column it would pass the following to the macro:
.AutoFilter Field:=8, Criteria1:="Severity 1" .Range("A1:AE1").AutoFilter Field:=5, Criteria1:=>100

Now inside the macro I don't have a problem parsing out the data in to 2 separate lines:
AutoStr1 = " Field:=8, Criteria1:=""Severity 1"""
AutoStr2 = " Field:=5, Criteria1:=>90,Operator:=xlAnd, Criteria2:<100" (in the case of the first example)

The issue is when I attempt to use these 2 string variables in the AutoFilter function I'm getting the error mentioned in my original post.

With Worksheets("QCData")
.AutoFilterMode = False
.Range("A1:AE1").AutoFilter
.Range("A1:AE1").AutoFilter AutoStr1
.Range("A1:AE1").AutoFilter AutoStr2

The comment isn't able to substitute the AutoStr1/AutoStr2 commands as I want to below (pardon any syntax in the below - I am able to get it to work when I actually hardcode in the macro - I'm just trying to provide example:
AutoFilterMode = False
.Range("A1:AE1").AutoFilter Field:=8, Criteria1:="Severity 1"
.Range("A1:AE1").AutoFilter Field:=5, Criteria1:= >=90 _
Operator:=xlAnd, Criteria2:=<100

Hope this clarifies things for the community.

Thanks,
David

 

[davidem]

For anyone that might be trying to accomplish what I was...my solution is listed below. It has some very specific edits for filtering based on the input data. I stumbled across the BeforeRightClick, Range.Row & Range.Column functions to help solve my issue with dynamic cell addressing. This allowed me, based on the cell clicked by the user, to read the appropriate values to pass to my AutoFiltering. I'm sure this could be cleaned up but for now (v1.0) I can go to bed with a sense of accomplishment of having 8 different reports all dynamically addressable with far less macro coding than I thought necessary.

After much googling here's my "Franken-code". It's strong user communities such as Mr. Exel which allow lesser VBA'ers to hack together code that passes the user story for testing.

=============================
Option Explicit

Dim flag As Boolean
Dim SevLevel, Status_Type, MonVal, YearVal As String
Dim MonNum As Integer

Private Sub Worksheet_BeforeRightClick(<wbr>ByVal Target As Range, Cancel As Boolean)
flag = True
End Sub

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If flag = True Then
flag = False
Exit Sub
End If

If MsgBox("Continue to Data View?", vbYesNo + vbQuestion) = vbNo Then Exit Sub

YearVal = Cells(2, 2).Value
YearVal = Right(YearVal, 2)
MonVal = Cells(Target.Row, 1).Value
MonVal = Month(DateValue("01-" & MonVal & "-1900"))
MonVal = WorksheetFunction.Text(MonVal, "00")

Status_Type = Cells(4, Target.Column).Value

If Target.Row >= 5 And Target.Row <= 16 Then
SevLevel = Cells(3, 1).Value
ElseIf Target.Row >= 21 And Target.Row <= 32 Then
SevLevel = Cells(19, 1).Value
ElseIf Target.Row >= 37 And Target.Row <= 48 Then
SevLevel = Cells(35, 1).Value
End If

MsgBox "Filtering Parms" & vbNewLine & "Status Type: " & Status_Type & vbNewLine & "Severity: " & SevLevel & vbNewLine & "Month: " & MonVal & vbNewLine & "Year: " & YearVal

With Worksheets("QCData")
.AutoFilterMode = False
.Range("A1:AE1").AutoFilter
.Range("A1:AE1").AutoFilter Field:=8, Criteria1:=SevLevel
.Range("A1:AE1").AutoFilter Field:=10, Criteria1:=Status_Type
.Range("A1:AE1").AutoFilter Field:=31, Criteria1:="=" & MonVal & "*", _
Operator:=xlAnd, Criteria2:="=" & "*" & YearVal
' .Range("A1:AE1").AutoFilter Field:=10, Criteria1:="<>" & "Closed"
End With

Sheets("QCData").Select

End Sub<wbr>

 

[davidem]

I should have noted that the above code is just 1 of the 8 variations used on different sheets (right click on each sheet, view code and added to each different report sheet) - just thought I better add that detail. Just sharing 1 of 8 with the community. Take care, David