How do I determine a Function's callling Row Number.

[Mac Lingo]

I need to know what ROW a Function is called from.

I am copying a Row with this function call in it to many Rows. The function
has to act on the data in the called row.

The ACTIVECELL pointer stays constant over all the calls, so that isn't
useful. I suppose I could generate a Column of Row Numbers and pass that in
the actual function call, ...

But isn't there a more elegant solution?

Thanks,
Mac Lingo

 

[Mac Lingo]

There is probably a more elegant way to do this, but the following
Subroutine & Function does return it.
Mac

Sub Calling_Address(RowNr, ColNr)
' Return Calling RowNr and ColNr.
Dim TS As String
Const xCharacters = "ABCDEFGHIJKLMNOPQRSTWXYZ"
Const xNumbers = "0123456789"

Select Case TypeName(Application.Caller)
Case "Range"
TS = Mid(Application.Caller.Address, 2, 99)
ColNr = GetRowColNr(TS, xCharacters, 26, 0)
TS = Mid(TS, 2, 999)
RowNr = GetRowColNr(TS, xNumbers, 10, 1)
Case Else
RowNr = -1
ColNr = -1

End Select

End Sub ' Calling_Address

Function GetRowColNr(ARG_STRING, TEST_CHARS, POWER, OFFSET) As Integer
' Take character of ARG_TYPE off ARG_STRING and return its
' numeric equalivent.
' This is to decode R1C1 type addresses.

Dim TS As String
Dim PTR As Integer

Pull_Number = 0

Do While Len(ARG_STRING)
TS = UCase(Left(ARG_STRING, 1))

PTR = InStr(TEST_CHARS, TS)
If (PTR) Then
Pull_Number = Pull_Number * POWER + (PTR - OFFSET)
Else
Exit Function
End If

ARG_STRING = Mid(ARG_STRING, 2, 99)
Loop



End Function ' GetRowColNr

 

[Tom Ogilvy]

Same as the last time you asked.

dim rng as Range
set rng = application.Caller
myrow = rng.row