What's wrong with this code? Countif

[Paul Turner]

I'm trying to calculate how often the digits 1,2,3,4,5 appear in a range. Then I want to do the same for a filtered range. Here is the code:

Set rng = Worksheets("Raw Data").Range("R2:W" & Lastrow)
Worksheets("Analysis").Range("B88").Value = Application.WorksheetFunction.CountIf(rng, 1)
Worksheets("Analysis").Range("C88").Value = Application.WorksheetFunction.CountIf(rng, 2)
Worksheets("Analysis").Range("D88").Value = Application.WorksheetFunction.CountIf(rng, 3)
Worksheets("Analysis").Range("E88").Value = Application.WorksheetFunction.CountIf(rng, 4)
Worksheets("Analysis").Range("F88").Value = Application.WorksheetFunction.CountIf(rng, 5)

ActiveSheet.Range("$A$1:$W$" & Lastrow).AutoFilter Field:=1, Criteria1:="Tue"
Set rng = Worksheets("Raw Data").Range("R2:W" & Lastrow).SpecialCells(xlCellTypeVisible)
Worksheets("Analysis").Range("B86").Value = Application.WorksheetFunction.CountIf(rng, 1) ********This causes an error - "Unable to get the Countif property of the Worksheet function class
Worksheets("Analysis").Range("C86").Value = Application.WorksheetFunction.CountIf(rng, 2)
Worksheets("Analysis").Range("D86").Value = Application.WorksheetFunction.CountIf(rng, 3)
Worksheets("Analysis").Range("E86").Value = Application.WorksheetFunction.CountIf(rng, 4)
Worksheets("Analysis").Range("F86").Value = Application.WorksheetFunction.CountIf(rng, 5)

I can't see why there should be an issue - the bottom block of code is basically the same as the top block except for filtering on the first column. Any ideas?

 

[Cubist]

it's possible that there are no visible cells that meet the criteria, which could cause issues with the CountIf function.