Counta Of Filtered Data - Errors In VBA Formula (Poor adaptation)

[Ark68]

Clearly I'm having, in addition to many things, working with filtered data. This may be related to this post.

Consider the code attached. The line in red, unsurprisingly, is giving me an error ("Expected: expression") I am trying to calculate the number of instances of the value "OK" from column J of a filtered worksheet. I found this solution with a Google search, but I had to adapt it from the demonstrated worksheet formula to a VBA formula. Clearly I failed. Woul anyone like to help me correct my adaptation to work for me?

Sub inspect1()
    Dim txt_model As String
    Dim wb_catalogue As Workbook
    Dim ws_ifm As Worksheet
    Dim d As Double
    Dim lstrow As Long
    Dim f_rowcnt As Long
    Dim mccntok As Long
    
    Set wb_catalogue = ThisWorkbook
    Set ws_ifm = wb_catalogue.Worksheets("IFM (M)")
    
    With ws_ifm
        .AutoFilterMode = False
        lstrow = .Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
        Debug.Print "Last row: " & lstrow
        txt_model = InputBox("Enter Model Name: ", "Index")
        If txt_model = "" Then Exit Sub
        .Range("A2").AutoFilter Field:=1, Criteria1:=txt_model
        Stop
        f_rowcnt = [subtotal(103,A:A)] 'thanks mumps
        Debug.Print f_rowcnt
        mccntok = worksheetfunction(sumproduct(subtotal(3,offset("J3:J" & lstrow)-min(row("J3:J" & lstrow)),,1,)),N("J3:J"&lstrow)="OK")))
    End With
End Sub

 

[Skyybot]

I believe the error is due to syntax. Application.WorksheetFunction.Sum(Range("A1:A10")

Application.WorksheetFunction property (Excel)

Office VBA reference topic

learn.microsoft.com

 

[Ark68]

Hi Skyybot, thank you for your input. If I understand what you are pointing out, I updated that line as:

mccntok = worksheetfunction(sumproduct(subtotal(3,offset(.Range("J3:J" & lstrow))-min(row(.Range("J3:J" & lstrow))),,1,)),N(.Range("J3:J" & lstrow))="OK"))

But still get the error. The error is highlighted in blue

 

[Ark68]

Correction?

mccntok = worksheetfunction.sumproduct(subtotal(3,offset(.Range("J3:J" &amp; lstrow))-min(row(.Range("J3:J" &amp; lstrow))),,1,<u><span><strong>))</strong></span></u>,N(.Range("J3:J" &amp; lstrow))="OK"))

 

[Skyybot]

mccntok = Application.WorksheetFunction.Subtotal(3, Range("A1:A10")) ' <-Change A1:A10 to your Range