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! 😉