'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

 

[Alex Blakenburg]

You are using fndrow for 2 entirely different things.

Usage 1 - as a long variable

    Dim f_rowcnt As Long, lstrow As Long, floop As Long, fndrow As Long, i As Long
    fndrow = -1
    fndrow = i

Usage 2 - as a Function

myrow = fndrow(ttle, tmdl)
' Calling
Function fndrow(ttle As String, tmdl As String) As Long

You need to change the name of one of them. ie if you opt to change the first. Remove fndrow As Long and change the other 2 lines from fndrow to myrow.

 

[Ark68]

Thank you Alex, you indeed found the culprit. I had totally forgotten that I had used fdrow already in another context. Changing my function name seems to have solved the problem. Thank you all for helping me through this.

 

[Alex Blakenburg]

Thanks for letting us know. Glad we could help.

 

[HighAndWilder]

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?

To avoid a loop you could do this.

Public Sub subFindRow()
Dim lngRow As Long
Dim ttle As String
Dim tmdl As String
Dim arr() As Variant
Dim ws_ifm As Worksheet

    Set ws_ifm = Sheets("Sheet2")
  
    ttle = "a"
  
    tmdl = "b"
  
    arr = Evaluate("LET(a," & ws_ifm.Name & "!" & ws_ifm.Range("S3:T" & ws_ifm.Cells(Rows.Count, "T").End(xlUp).Row).Address & ",TAKE(FILTER(ROW(a)," & _
      "(INDEX(a,,1)=" & """" & ttle & """" & ")*(INDEX(a,,2)=" & """" & tmdl & """" & "),0),1))")
  
    lngRow = arr(1)
  
    MsgBox tmdl & " found at row : " & lngRow, , ttle

End Sub

Expected Sub Function.xlsm
	S	T
2	S	T
3	a	b
4	h	i
5	k	a
6	m	r
7	l	y
8	z	p
9	a	i
10	x	h
11	o	u
12	w	l
13	k	r
Sheet2

 

[Ark68]

Thank you @HighAndWilder for the alternative!