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

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

How do I determine a Function's callling Row Number. Here is "A" Solution.
 

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

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

Same as the last time you asked.

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

--
Regards,
Tom Ogilvy

"Mac Lingo" wrote in message
link.net...
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