Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Please help me find the problem with this
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? |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Please help me find the problem with this
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? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Problem with 'Find' | Excel Discussion (Misc queries) | |||
Find Next problem | Excel Programming | |||
problem with FIND | Excel Worksheet Functions | |||
Find problem | Excel Programming | |||
find problem | Excel Programming |