CODEWITHSUNDEEP
excelvba
CuriousGeorge
CuriousGeorge

Reputation: 49

How to use Count If function inside of a sub?

I am trying to count the number of times the string shows up in the range and return to worksheet 1. I am assuming this can be done in a Function () but I'm don't know how to write out the syntax.

Sub DistributedApps()
    Dim LastRow As Long
    Dim Dist As Long

    LastRow = Worksheets(3).Cells(Rows.Count, 25).End(xlUp).Row

    Dist = Application.Worksheets(3).WorksheetFunction.CountIf(Range("Y1:Y" & LastRow), "Distributed Apps")
    Worksheets(1).Range("N66:P69").Value = Dist
End Sub

Object doesn't support this property or method

Upvotes: 1

Views: 68

Answers (2)

Philip Axt
Philip Axt

Reputation: 56

I think this will work.

Sub DistributedApps()
    Dim LastRow As Long
    Dim Dist As Long

    LastRow = Worksheets(3).Cells(Rows.Count, 25).End(xlUp).Row

    Dist = Application.WorksheetFunction.CountIf(Worksheets(3).Range("Y1:Y" & LastRow), "Distributed Apps")
    Worksheets(1).Range("N66:P69").Value = Dist

End Sub

Upvotes: 2

Mathieu Guindon
Mathieu Guindon

Reputation: 71217

Dist = Application.Worksheets(3).WorksheetFunction...

The Worksheets property is returning an Object that could be either a Sheets collection (if given an array of sheet names) or a Worksheet object (if given one sheet name, or a sheet index), so you're getting a Worksheet object, but VBA only knows this at run-time.

Move that knowledge to compile-time by introducing a local Worksheet variable; note that Application.Worksheets will give you the sheets collection of whatever workbook is currently active, so it might be a good idea to make that more explicit by qualifying the member call with an actual Workbook object:

Dim sheet As Worksheet
Set sheet = ActiveWorkbook.Worksheets(3)

Now that VBA knows what interface this object has, the editor can help you: when you type the dot in sheet., you'll get a list of all members of the Worksheet interface - and see that none of them is WorksheetFunction: that is why error 438 is raised at run-time, the object doesn't support this property.

The Application object does have a WorksheetFunction member though, so this will work.

lastRow = sheet.Cells(sheet.Rows.Count, 25).End(xlUp).Row
dist = Application.WorksheetFunction.CountIf(sheet.Range("Y1:Y" & lastRow), "Distributed Apps")

Note that the Range member call inside the CountIf argument list is also explicitly qualified with the sheet object. Without this qualifier, Range would be referring to whatever the ActiveSheet is1, and since you don't want to need to Activate any sheets for this to work, using an explicit worksheet object as a qualifier ensures you're computing the count off the correct sheet.

1Unless that code is written in the code-behind for a Worksheet module - in which case the implicit qualifier is Me and the unqualified Range call is referring to that worksheet.

Upvotes: 3

Related Questions