Trouble with CountIF Function in VBA, I think

[MrBrian]

Hi Guys,

I spent a bunch of time writing a big macro to pull data from one sheet of my workbook onto another in a very fiddly way, but I can't even get it to run due to runtime errors. Here's the first bit, which runs into 'Error 438: Object doesn't support this property or method' on the line that starts "xLines = ...". I've already tried jiggering it around to get it to work a bit. My original code, on the line below, returns "Error 424: Object required'. SaleSheetData is a named data range, found on sheet "Sale01", with Workbook scope. I tried the Google, and a forum search, but didn't find anyone who'd done whatever foolish thing I seem to have done.

Dim numLines As Integer
Dim xLines As Integer
Dim yLines As Integer
Dim blankLines As Integer
Dim i As Integer
Dim wSale As Worksheet, wPO As Worksheet, wTemplate As Worksheet
Dim matchX As String, matchY As String
Dim SaleSheetData As Range




Set wSale = Worksheets("Sale01")
Set wPO = Worksheets("PO")
Set wTemplate = Worksheets("Templates")
Set SaleSheetData = wSale.Range("SaleSheetData")
matchX = "Ducks"
matchY = "Chickens"


Application.ScreenUpdating = False


xLines = Application.WorksheetFunctions.CountIf(Range(SaleSheetData.Offset(0, 80), SaleSheetData.End(xlDown)), matchX)
yLines = WorksheetFunctions.CountIf(WorksheetFunctions.Index("SaleSheetData", 0, 80), matchY)
numLines = xLines + yLines
blankLines = WorksheetFunctions.CountA(wPO.Range("B19:B1000"))

This part of the macro is just supposed to get the number of chickens and ducks from the sale sheet, to be used for a number of things later in the macro, and the chicken/duck/other column is column 80 (don't ask) in the range. Hopefully I can fix whatever is wrong globally, but this is also the only section that calls worksheet functions, so if there's a better way to do it wholly within VBA, I'm open to that, too.

Thanks in advance!

[Paul_Hossler]

Just guessing here

You have a Range variable named SaleSheetData, and a Named Range called SaleSheetData (I never use the same name for things since I get confused)

Set SaleSheetData = wSale.Range("SaleSheetData")

but it looks like you're inconsistent here when you use the object variable and the name of the range …

xLines = Application.WorksheetFunctions.CountIf(Range(SaleSheetData.Offset(0, 80), SaleSheetData.End(xlDown)), matchX)

yLines = WorksheetFunctions.CountIf(WorksheetFunctions.Index("SaleSheetData", 0, 80), matchY)

Maybe you want something like this??

xLines = Application.WorksheetFunctions.CountIf(Range(SaleSheetData.Offset(0, 80), SaleSheetData.End(xlDown)), matchX)

yLines = Application.WorksheetFunctions.CountIf(Application.WorksheetFunctions.Index(SaleSheetData, 0, 80), matchY)

No data to test with so you'll have to give this a try yourself. Although I'm not sure about your Range(…)

You can simplify a little

With 
Application.WorksheetFunctions

    xLines = .CountIf(Range(SaleSheetData.Offset(0, 80), SaleSheetData.End(xlDown)), matchX)

    yLines = .CountIf(.Index(SaleSheetData, 0, 80), matchY)

End With

[Aflatoon]

It's Application.WorksheetFunction and not Application.WorksheetFunctions...

[Paul_Hossler]

It's Application.WorksheetFunction and not Application.WorksheetFunctions...

…..

[MrBrian]

It's Application.WorksheetFunction and not Application.WorksheetFunctions...

I am both sad that that was the issue, and happy that that fixed the macro. Thanks so much, Aflatoon! And thanks, Paul, for the suggestion on the With Application.WorksheetFunction, that's made the code much cleaner! Also, I'd just included the "yLines=..." line above to show my original code, so you could let me know if I was moving in the wrong direction. I've since updated that line to be similar to the "xLines" line, and the macro's off and running!

Thanks again!!

[MrBrian]

Actually on second thought I made the "xLines" line like the "yLines" line, since the With makes it so much cleaner.