Error Trying To Get Count of String Value Of A Column In A Filtered Worksheet Using WorksheetFunction.subtotal

[Ark68]

I am experimenting with the code below and failing with my results. The line highlighted in red is giving me an "Unable to get the Subtotal property of the Worksheetfunction class" as I try to get a count the number of occurrences of the string "MIN" in column N of my filtered data worksheet. Any thoughts on the correction needed. Are there any other problems with this code?

Sub compDateData(ByRef LDate As Date)
    With ws_ops
        .AutoFilterMode = False
        tarRow = 32
        dtf = LDate
        For i = 1 To 7
            cntDtOps = Application.WorksheetFunction.CountIf(.Columns(2), dtf)
            .Range("A:T").AutoFilter Field:=2, Criteria1:="=" & dtf
            cntstmin = Application.WorksheetFunction.Subtotal(103, .Columns(14), "MIN")
            sumsalt = Application.WorksheetFunction.Subtotal(109, .Columns(14))
            cntsdmin = Application.WorksheetFunction.Subtotal(103, .Columns(15), "MIN")
            sumssand = Application.WorksheetFunction.Subtotal(109, .Columns(15))
            ws_weeks.Cells(tarRow, 14) = Format(dtf, "DDD DD-MMM-YY")
            ws_weeks.Cells(tarRow, 15) = cntDtOps
            ws_weeks.Cells(tarRow, 16) = sumsalt
            ws_weeks.Cells(tarRow, 17) = cntstmin
            ws_weeks.Cells(tarRow, 18) = sumsand
            ws_weeks.Cells(tarRow, 19) = cntsdmin
            tarRow = tarRow + 1
            dtf = dtf + 1
        Next i
    End With
End Sub

 

[Ark68]

I figured out the problem here. 103 is CountA function. I'm needing a Countif function (countif value = "MIN"), which I don't believe exists for the SubToptal function.
Back to Google unless someone can suggest a means of Countif of filtered data. I'll post back if I find a solution.

 

[Ark68]

I tried this suggestion ...

cntstmin = Application.WorksheetFunction.CountIf(.Cells.SpecialCells(xlCellTypeVisible), "MIN")

but this leaves me with an "Unable to get the CountIf property of the WorksheetFunction class"

 

[Ark68]

I've cross posted this question here.

 

[RoryA]

You could just use COUNTIFS adding in the same criteria you are filtering on.

 

[Ark68]

Thank you Rory. If I understand correctly you are suggesting something like this on unfiltered data?

myvalue = WorksheetFunction.CountIfs(ws_data.columns(1), lupDate, ws_data.columns(14), "MIN")

 

[RoryA]

Yes. It doesn't matter whether the data is filtered or not.