'Expected Sub, Function, or Property' Error Calling A Function

[Ark68]

What would be a likely cause of an "Expected Sub, Function, or Property" error with the highlighted line in the code snippet below. Anyone able to help me with a solution?

...
Do While .Cells(tcr, 27) <> ""
     ttle = .Range("C" & i)
     tmdl = .Range("AA" & tcr)
     fndrow ttle, tmdl
     MsgBox tmdl & " found at row: " & fndrow, , ttle
Loop
...

And then I have this function ...

Function fndrow(ttle As String, tmdl As String) As Long
    Dim rng As Range
    Dim rowNumber As Long
    Dim found As Boolean
  
    ' Define the range to search
    Set rng = ws_ifm.Range("A3:A" & ws_ifm.Cells(ws.Rows.count, "C").End(xlUp).Row)
  
    ' Initialize variables
    found = False
  
    ' Loop through each cell in column C
    For Each cell In rng
        ' Check if Column C equals Value1 and Column F equals Value2 in the same row
        If cell.Value = tmdl And ws_ifm.Cells(cell.Row, "C").Value = ttle Then
            rowNumber = cell.Row
            found = True
            Exit For ' Exit loop once a match is found
        End If
    Next cell
End Function

 

[jimrward]

Your function fndrow isn’t returning anything try adding a line before end function
Fndrow= rownumber
End Function

 

[Ark68]

Hi jimrward, thank you. I added that line, but I'm still getting that error. For certian what you pointed out would have been the next encountered error.

 

[jimrward]

Try in your function call, replace fndrow ttle, tmd
To
Myrow = fndrow(ttle,tmdl)
And use myrow in your msg statement instead of fndrow

 

[Joe4]

In addition to the update to the Function code jimrward recommended, you are also not calling/using it correctly.
A User Defined Function works the same as other Excel functions - you need to either call or display it in a message box. A function call by itself will not do anything.
It would be just like having a line like this:

LEFT(Range("A1",2)

You are calculating something, but not actually doing anything with the calculation (i.e. setting it equal to a variable, assigning it to a range, displaying it).
And "fndrow" is not a variable, it is a function. You wouldn't do this:

MsgBox Left

that is meaningless.

You should structure it like this, capturing the calculated value in a variable, and using that variable in your message box:

Do While .Cells(tcr, 27) <> ""
    ttle = .Range("C" & i)
    tmdl = .Range("AA" & tcr)
    myRow = fndrow(ttle,tmdl)
    MsgBox tmdl & " found at row: " & MyRow, , ttle
Loop

EDIT: My slow typing skills come back to haunt me again, as jimrward posted while I was typing. We are saying the same thing. I just included a little more in depth explanation of the logic you need to employ.

 

[Ark68]

Joe, I appreciate the the added explanation, that really helped me understand the logic. I think I was treating a function similar to that of calling a procedure. But I get it now.
I made the changes, but now I'm getting an "Expected array" error with

myrow = fndrow(ttle, tmdl)

 

[Joe4]

Can you let us know the exact values you are using for "ttle" and "tmdl"?

And your function uses references to sheet objects named "ws_ifm" and "ws", but I do not see those defined or set anywhere in your function code.
Are you defining global variables for this and setting its values elsewhere in your code?

 

[Ark68]

Hi Joe, thank you for your continuedd support. The values for ttle and tmdl are declared within the procedure (Dim) as strings. Perhaps they need to be global? They are calculated in the loop just prior to the function being called. I can't get their values as the code breaks with the error before executing the code.

The ws_ifm is a globally declared worksheet. "ws" was an error that I replaced with ws_ifm as a correction.

 

[Joe4]

No, the values do not need to be declared globally, if they are declared and set right in the procedure
You can easily get the values of those variables with a Message Box just before you call the function, i.e.

Do While .Cells(tcr, 27) <> ""
    ttle = .Range("C" & i)
    tmdl = .Range("AA" & tcr)
    MsgBox "ttle is: " & ttle & vbCrLf & "tmdl is: " & tmdl
    MyRow = fndrow(ttle, tmdl)
    MsgBox tmdl & " found at row: " & MyRow, , ttle
Loop

Then, if you run it, it should pop-up a message box with their values before trying to use them in your function (where you are getting your error).

 

[Ark68]

I added that line Joe, but again, the code execution doesn't get that far. This snippet of code is in a procedure called "acqmodel". The procedure is called, but as soon as the procedure is called, the error prevents that procedure from even starting. When I debug the error, the "sub acqmodel ()" procedure is highlighted yellow as to where the code is waiting. If I try to advance the code from this point with F8, the expected array error pops up with 'fndrow' highlighted in blue in the line myrow = fndrow (ttle, tmdl).

Sub acqmodel()
    Dim f_rowcnt As Long, lstrow As Long, floop As Long, fndrow As Long, i As Long
    Dim response
    Dim mcval As Long
    Dim c1 As String, c2 As String
    Dim ifmTxtModel As String
    Dim drow As Long, trow As Long
    Dim grp As String, lengrp As Long, noCommaLength As Long, commacnt As Long, nopart As Long
    Dim cntPL As Long, nnames As Long
    Dim host As String, partialName As String, mfname As String
    Dim result As Boolean, exists As Boolean
    Dim FolderPath As String, inimodel As String
    Dim nrngUXDump As Range, foundCell As Range
    Dim ttle As String, tmdl As String, retrow As Long, tcr As Long
    Dim myrow As Long
    
    f_rowcnt = 0
    With ws_ifm
        If .AutoFilterMode Then .AutoFilterMode = False
        lstrow = .Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
        .Range("A2").AutoFilter Field:=1, Criteria1:=txt_model
        f_rowcnt = [subtotal(103,A:A)] - 2
        Debug.Print f_rowcnt
        If f_rowcnt = 0 Then
            response = MsgBox(txt_model & " does not exist in the catalogue." & Chr(13) & "Proceed to model entry?", vbYesNo, "Error")
            If response = vbYes Then
                lstrow = .Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
                reset_dump
                open_folder txt_model
            'open webspage
                If Right(txt_model, 1) = "." Then
                    ifmTxtModel = Left(txt_model, Len(txt_model) - 1)
                Else
                    ifmTxtModel = txt_model
                End If
                SearchWebPage ifmTxtModel
            Else
                mbevents = False
                frm_reset
                mbevents = True
            End If
            Exit Sub
        Else
            reset_dump
            .UsedRange.SpecialCells(xlCellTypeVisible).Copy _
                Destination:=ws_dump.Range("A2")
            ws_dump.Rows("2:3").EntireRow.Delete
            f_rowcnt = WorksheetFunction.CountA(ws_dump.Columns(1))

            'acquire database row number
            For floop = 2 To f_rowcnt '- 1
                c1 = ws_dump.Range("A" & floop)
                c2 = ws_dump.Range("C" & floop)
                fndrow = -1
                For i = 3 To lstrow
                    If ws_ifm.Cells(i, 1).Value = c1 And ws_ifm.Cells(i, 3).Value = c2 Then
                        fndrow = i
                        ws_dump.Range("K" & floop) = i
                        Exit For
                    End If
                Next i
            Next floop

            'open model folder
            open_folder txt_model

            'open webspage
            If Right(txt_model, 1) = "." Then
                ifmTxtModel = Left(txt_model, Len(txt_model) - 1)
            Else
                ifmTxtModel = txt_model
            End If
            SearchWebPage ifmTxtModel
         End If
    End With

    'prepare listbox data range
    With ws_dump
        .Activate
        lstrow = ws_dump.Cells(ws_dump.Rows.count, "A").End(xlUp).Row
        .Range("C2:C" & lstrow).Copy Destination:=.Range("U2")
        'assess model collected, "" if title not model checked
        For i = 2 To lstrow
            mcval = .Range("J" & i)
            If IsNumeric(mcval) Then
                .Range("W" & i) = "YES"
            Else
                .Range("W" & i) = "NO"
            End If
        Next i
        .Range("U" & lstrow + 1) = "ADD Title"
        
    'determine host (primary or secondary)
        For i = 2 To lstrow
            .Range("Z2:AB15").Clear
            .Range("AA2") = txt_model
            'check if primary
            If .Range("B" & i) = "" Then 'no participants so only primary
                .Range("X" & i) = "" 'leave empty. Populate with alternate when alternate host's title (alphabetical)
                'underline the model name (in database) as host if not already underlined
                If .Range("A" & i).Font.Underline = xlUnderlineStyleNone Then
                    drow = .Range("K" & i)
                    ws_ifm.Range("A" & drow).Font.Underline = xlUnderlineStyleSingle
                End If
                .Range("Z2") = "HOST"
            Else 'inspect participants
                'gather names based on comma count
                grp = .Range("B" & i)
                lengrp = Len(grp)
                noCommaLength = Len(Replace(grp, ",", ""))
                commacnt = lengrp - noCommaLength
                If commacnt = 0 Then 'only one name
                    nopart = 2
                    .Range("AA3") = grp
                    '.Range("AA2:AA3").Sort key1:=.Range("AA2"), order1:=xlAscending, Header:=xlNo
                    grp = .Range("AA2") & ", " & .Range("AA3")
                Else
                    nopart = commacnt + 1
                    ExtractNamesToColumn grp
                End If
                  
                'integrity check
                cntPL = Application.WorksheetFunction.CountA(ws_dump.Columns("AA"))
                'If cntPL <> nopart Then Stop '(count of participant names in dump not equal to names in database cell)
                'get the first name in the list alphabetically
                host = FirstNameAlphabetically(grp)
                host = RTrim(host)
                MsgBox "The host should be: " & host
                Set foundCell = ws_dump.Columns("AA").Find(host, LookIn:=xlValues)
                'MsgBox "Host " & host & " in cell " & foundCell.Address
                foundCell.Offset(0, -1).Value = "HOST"

                'determine in primary comes before alternate to determine host
                result = IsAlphabeticallyBefore(txt_model, host)
                If result = False Then
                    .Range("X" & i) = host 'if the altname (underlined) comes before primary, than altname hosts, else primary hosts (underlined)
                End If
                '### check and underline altname in database participants cell of
            End If
            
            'populate title based on whether in the folder of not (folder should already be open to txt_model
            'partialName
            partialName = .Range("C" & i)
            'folder path
            If .Range("X" & i) = "" Then 'txt_model is the host
                mfname = Trim(txt_model)
                If Right(mfname, 1) = "." Then mfname = Left(mfname, Len(mfname) - 1)
            Else 'alternate is the host
                mfname = Trim(host)
                If Right(mfname, 1) = "." Then mfname = Left(mfname, Len(mfname) - 1)
                'check altnames directory for title (partial)
            End If

            inimodel = Left(mfname, 1)
            FolderPath = "O:\IFM\" & inimodel & "\" & mfname
            exists = FileExistsWithPartialName(FolderPath, partialName)
            If exists Then
                .Range("V" & i) = "OK"
                trow = .Range("K" & i)
                'update txt_model entry
                ws_ifm.Rows(trow).EntireRow.Font.Color = RGB(84, 130, 53)
                ws_ifm.Range("I" & trow).Value = "OK"
                'update participants
                tcr = 3
                Do While .Cells(tcr, 27) <> ""
                    ttle = .Range("C" & i)
                    tmdl = .Range("AA" & tcr)
                    MsgBox "ttle: " & ttle & Chr(13) & "tmdl: " & tmdl
                    myrow = fndrow(ttle, tmdl)
                    MsgBox tmdl & " found at row: " & myrow, , ttle
                Loop
            'acknowledge participants collection
                'create participant list (dump A
            End If
        Next i

        'create named range of dump (U:X)
        'nrngUXDump.Delete
        .Range("U2:X" & lstrow + 1).Name = "nrngUXDump"
        
        'check value of txt_model to ensure periods exist
        With IFM_Title
            .tbx_titlecnt.Value = WorksheetFunction.CountIf(ws_dump.Columns(1), txt_model)
            .tbx_checkedcnt.Value = WorksheetFunction.CountIf(ws_dump.Columns("W"), "YES")
            .tbx_collectedcnt.Value = WorksheetFunction.CountIf(ws_dump.Columns("V"), "OK")
            With .lbx_collections
                .columnCount = 4 'collected, title, primary, model checked
                .ColumnWidths = "140, 30,30,68"
                .List = Application.Range("nrngUXDump").Value
            End With
        End With
    End With
End Sub