|I'm still slightly confused. |
Your formula refers to Row 18 twice, both in the logical_test portion and in the value_if_true portion. Is this the same for all of the formulae that you are trying to extract the row number from? If not, which of the 2 row references do you want?
This version of the UDF should handle your current example by extracting the row number from the logical_test portion.
My concern is that it is written based on certain characters in the formula and may not work generically. Basically I'm searching for numbers between the first $ and the second =. i.e. In the bold section below ...
That said, I'm not going to claim to completely understand what you are actually doing with this number but for some reason the INDIRECT function comes to mind.
With a row number in a cell, such as 18 in H34, these 2 formulae are equivalent:
=IF(INDIRECT("Sheet1!$E" & H34)="","",INDIRECT("Sheet1!$E" & H34))
Is this something you can use in your project? I'm thinking that you could change a value in a cell and the change would propagate through all INDIRECT references to that cell.
Anyway, here's the new version of the UDF:
Function GetRow(R As Range) As Variant
For Digit = InStr(2, R.Formula, "$", 1) To InStr(2, R.Formula, "=", 1)
On Error Resume Next
myRow = myRow & Mid(R.Formula, Digit, 1) * 1
GetRow = myRow