VBA - Autofilter 3 months per quarter basis using namedrange

[dellzy]

Hi Experts,

I got the below code from one of webpages in contexture. I amend a little bit here and there to meet my requirements. I want user to be able to see all the filtered data based on 3 months in the quarter number selected. At first I tested on a fix "Q1" as the choice and it works. But when I expanded the code to make it suitable to do necessary filter on the quarter choice, it pops-up an error "application defined or object-define error". Am not sure if I place the End If correctly either, because am not sure how the cycle would be. Appreciate some experts' help, please.

Sub FilterByQuarter()

Dim RngQ As Variant
Dim LastRow As Long
Dim Choice As Range
Dim CritQ As Range
Dim rngPIStaff As Range
Dim wsPIStaff As Worksheet
Dim wsNMLST As Worksheet


ActiveSheet.Unprotect Password:="0ps000"
LastRow = Range("A" & Rows.Count).End(xlUp).Row
Set wsPIStaff = Worksheets("PI_Staff")
Set wsNMLST = Worksheets("NMLST")
Set rngPIStaff = wsPIStaff.Range("A4:AC" & LastRow)

Range("D3").Select
Set Choice = Range("D3")
If Choice.Value = "Q1" Then
Set CritQ = Range("QUART1")
If Choice.Value = "Q2" Then
Set CritQ = Range("QUART2")
If Choice.Value = "Q3" Then
Set CritQ = Range("QUART3")
If Choice.Value = "Q4" Then
Set CritQ = Range("QUART4")
RngQ = CritQ.Value

End If
End If
End If
End If


wsPIStaff.Range("A4:AC" & LastRow).AutoFilter Field:=1, Criteria1:=Application.Transpose(RngQ), Operator:=xlFilterValues
ActiveSheet.Protect Password:="0ps000"
'End If
End Sub

 

[Andrew Poulsom]

Does this construct work for you?

    If Choice.Value = "Q1" Then
        Set CritQ = Range("QUART1")
    ElseIf Choice.Value = "Q2" Then
        Set CritQ = Range("QUART2")
    ElseIf Choice.Value = "Q3" Then
        Set CritQ = Range("QUART3")
    ElseIf Choice.Value = "Q4" Then
        Set CritQ = Range("QUART4")
    End If
    RngQ = CritQ.Value

 

[dellzy]

Hello Mr. Andrew,

I really thank you for giving me to group the If and End If properly. Though when I run the code it popped up another different error, eventually I found out what caused it and fixed it with your correct grouping. I found out that I cannot use ".Value" in the "Choice" after declaring Choice to store the value of Range ("C3") which is the validation list on quarter number. Below my amended code:-

If Choice = "Q1" Then        Set CritQ = wsNMLST.Range("QUART1")
    ElseIf Choice = "Q2" Then
        Set CritQ = wsNMLST.Range("QUART2")
    ElseIf Choice = "Q3" Then
        Set CritQ = wsNMLST.Range("QUART3")
    ElseIf Choice = "Q4" Then
        Set CritQ = wsNMLST.Range("QUART4")
    End If
    RngQ = CritQ.Value

At last, it works as I wanted it to be! Yeayy!! Feels so great to have this done finally!.