(Solved) – Excel VBA – WorksheetFunction.CountA error – Object required


folks. I’m a programming newbie, trying to write a macro to extract some rows of data from multiple workbooks and compile them into a new workbook, then graph them. I have figured out how to loop through the source spreadsheets, and a few other things, but right now I’m stuck on a couple of places. (I’ll ask my different questions in different threads for clarity.)

This question is about an error when using WorksheetFunction.CountA to get the number of a list of items I’m searching for. I want to know the number so that I can know when I have found all of my search strings. If I can’t find them all, I want to inform the user and quit.

Following advice on this question, I wrote a snippet to test the CountA function. I get an “object required” error. I have reviewed several threads on that topic, but I’m still not getting it. Here is my code snippet:

Sub a_test_kpi_count()

Dim kpi_list_count As Integer

 Set kpi_list_count = Application.WorksheetFunction.CountA("kpi_list")

 MsgBox "There are " & kpi_list_count & "kpis in the list."

End Sub

When I run the code, the editor stops with either “kpi_list_count” OR “CountA” highlighted. Well, THAT’S helpful!

I should add that “kpi_list” is a named range on the worksheet where the macro lives. However, I get the same error when I specify the range in this manner:

Set kpi_list_count = Application.WorksheetFunction.CountA("K3:K7")

Ergo, I don’t think the named range is my problem.

A nudge in the right direction would be much appreciated!

OR, feel free to call me an idiot and point out my obvious error! 😉

Leave a Reply

Your email address will not be published. Required fields are marked *