VBA code for filtering using 5 variables criteria

[chiidzzz]

Hi, how can I add 4 more variable criteria to this code, is it possible?

ActiveSheet.Range(Range("A2"), Range("R2").End(xlDown)).AutoFilter Field:=1, Criteria1:=ThisYearID

criteria2 should be :ThisYearID-1
criteria3 should be :ThisYearID-2
criteria4 should be :ThisYearID-3
criteria5 should be :ThisYearID-4

 

[NoSparks]

Do all those criteria together equate to >ThisYearID-5 ?

 

[chiidzzz]

Yes

 

[chiidzzz]

So how should I write it?

 

[NoSparks]

did you try Criteria1:=">" & ThisYearID-5

 

[chiidzzz]

it worked, thank you

 

[chiidzzz]

Sub test_calculateval()
Dim rnData, v, r As Long, ThisYearID, Hcount As Long, Hometeam As String
ThisYearID = Sheet5.Cells(2, 1).Value - 1
Hometeam = Sheet5.Cells(2, 5)
With Sheet1
    Set rnData = Range(Range("A2"), Range("R2").End(xlDown))
           With rnData
            Rows("1:1").Select
            Selection.AutoFilter
            ActiveSheet.Range(Range("A2"), Range("R2").End(xlDown)).AutoFilter Field:=1, Criteria1:=">" & ThisYearID - 5
            ActiveSheet.Range(Range("A2"), Range("R2").End(xlDown)).AutoFilter Field:=5, Criteria1:=Hometeam
           End With
           
    Set v = ActiveSheet.AutoFilter.Range.Offset(1).SpecialCells(xlCellTypeVisible).Cells(1, 2).Row
    Set r = ActiveSheet.AutoFilter.Range.Offset(1).SpecialCells(xlCellTypeVisible).Cells(1, Rows.count).Row
    
  For i = v To r
  
    If ActiveSheet.UsedRange.SpecialCells _
        (xlCellTypeVisible).Areas.Columns(11).Cells(i, 11) = "H" Then
            Hcount = Hcount + 1
    End If
    
  Next
  
End With


MsgBox Hcount


End Sub

Any idea how to fix the counting of "H" in column 11?

 

[NoSparks]

You should use Option Explicit and declare all your variables.
Have a look at this page, in particular the part about Variables Declared With One Dim Statement.

Seldom is there a need to Select anything in VBA.
Your Row("1:1").Select makes me think row 1 is the data headers.
See if this helps

Sub test_calculateval()
    Dim rnData As Range, Hcount As Long
    Dim ThisYearID As Long, Hometeam As String
    Dim cel As Range
    
ThisYearID = Sheet5.Cells(2, 1).Value - 1
Hometeam = Sheet5.Cells(2, 5)

With Sheet1
    ' this assumes headers are in row 1
    Set rnData = .Range(.Range("A1"), .Range("R2").End(xlDown))
End With

With rnData
    .AutoFilter Field:=1, Criteria1:=">" & ThisYearID - 5
    .AutoFilter Field:=5, Criteria1:=Hometeam
End With
    
    For Each cel In rnData.Columns(11).SpecialCells(xlCellTypeVisible)
        If cel.Value = "H" Then
            Hcount = Hcount + 1
        End If
    Next cel

    MsgBox Hcount

End Sub

 

[chiidzzz]

This is just what I needed.
Thank you NoSparks for your effort and time.