2

I get a "Unable to get the Countif property of the Worksheetfunction class" error when using this code

    Windows("usertemp.xls").Activate
Selection.AutoFilter
ActiveSheet.Range("$A$1:$AO$18695").AutoFilter Field:=14, Criteria1:=Array( _
    "28", "BE", "CH", "DE", "FR", "JP", "NL"), Operator:=xlFilterValues
Dim Impats As Integer
Impats = Application.WorksheetFunction.CountIf(Range("AL:AL").SpecialCells(xlCellTypeVisible), "I")
MsgBox Impats
aoswald
  • 75
  • 2
  • 10

2 Answers2

3

CounIf does not accept a multi-area range. You need to loop over the Areas:

Dim impats As Long, r As Range
For Each r In Range("AL:AL").SpecialCells(xlCellTypeVisible).Areas
    impats = impats + WorksheetFunction.CountIf(r, "I")
Next
A.S.H
  • 29,101
  • 5
  • 23
  • 50
3

The SpecialCells(xlCellTypeVisible) will create a disjointed range and Countif does not play nice with disjointed ranges.

You will need to use a loop to loop through each criteria and add the Countifs together.

Try this:

Dim arr() as variant
Dim arrPart as variant
arr = Array("28", "BE", "CH", "DE", "FR", "JP", "NL")

Dim Impats As Integer

For Each arrPart in arr
    Impats = Impats + Application.WorksheetFunction.CountIfs(ActiveSheet.Range("AL:AL"), "I",ActiveSheet.Range("N:N"),arrPart)
Next arrPart
MsgBox Impats
Scott Craner
  • 148,073
  • 10
  • 49
  • 81