Barb,

IF cell H8 has a sheetname in it, then something like this would work:

Function MyRow(pattern As String) As Long
Dim myR As Range

Set myR = oWS.Range("C1:C15").Find(what:=pattern)
MyRow = myR.Row
End Function

Sub test()
ActiveCell.FormulaR1C1 = _
"=INDIRECT(RC8&""!$I$" & MyRow("Test") & """)"
End Sub

HTH,
Bernie
MS Excel MVP


"Barb Reinhardt" wrote in message
...
I have the following function:

Function MyRow(pattern As String)

oWB.Activate
oWS.Activate
Debug.Print "In MyRow function"
For i = 1 To 15
'Debug.Print i, Cells(i, "B").text, pattern
If Cells(i, "C").text Like pattern & "*" Then
MyRow = i
aWB.Activate
aWS.Activate
Debug.Print "MyRow = ", MyRow, Cells(i, "B").text, pattern
Exit Function
End If
Next i

End Function

I want to use it in the following formula

ActiveCell.FormulaR1C1 = _
"=FORMULATEXT(INDIRECT(RC8&""$I$13"",TRUE))"

In place of the 13. I know that the function is returning what I'm looking
for, but I just can't get it to work in the above formula. Can someone
assist?