problem with CountIf VBA (getting 'unable to get the CountIf property' error...)

[kbishop94]

I need the code to look at visible cells (ones that have not been filtered) cells in column 'T' starting at row 31 and down to where I know there is no more data... I'm using '2000' just to to make sure that its looking all the way down to the bottom.

Within this range, I want the CountIf to count the number of times that it finds the string "RMA" and then put that number in cell T28.

Here is my code:

ActiveWorkbook.Worksheets("MTHLY_REPORTS").Range("T28").value = Application.WorksheetFunction.CountIf(Range("T31:T2000").SpecialCells(xlCellTypeVisible), "RMA")

I'm getting the error: Run-time error '1004' Unable to get the CountIf property of the WorksheetFunction class.

Red is cell T28 where I want the CountIf value to appear, and blue is the target string ("RMA") that I want the CountIf to find and count. (it should be a '1' that is placed into T28.)


Thanks for any help or suggestions!

 

[Legacy 143009]

CounIf does not accept a multi-area range. A possible workaround would be:

Dim count AS Long, r As Range
For Each r In Range("T31:T2000").SpecialCells(xlCellTypeVisible).Areas
    count = count + WorksheetFunction.CountIf(r, "RMA")
Next
ActiveWorkbook.Worksheets("MTHLY_REPORTS").Range("T28").Value = count

 

[kbishop94]

CounIf does not accept a multi-area range. A possible workaround would be:

Dim count AS Long, r As Range
For Each r In Range("T31:T2000").SpecialCells(xlCellTypeVisible).Areas
    count = count + WorksheetFunction.CountIf(r, "RMA")
Next
ActiveWorkbook.Worksheets("MTHLY_REPORTS").Range("T28").Value = count

Excellent. That works perfectly. Thank you, Flashbond

 

[Legacy 143009]

Glad it did work. Thanks for the feedback