User-Defined Function with a twist 1

[R3D3]

I want to write my own function which takes details from same rows but different columns on another sheet.
I dont want to pass the Row in the function.
Is there a way of identifying which row the function is recalculating. I thought it would be Activecell but of course this is the cell where the cursor is - Recalculating all my other cells containing this function.

=GetIt(NamedField)

Public Function GetIt(FieldDetails)
Application.Volatile
GetCol = FieldDetails.Column
GetRow = ActiveCell.Row
GetIt = ActiveWorkbook.Sheets("OtherSheet").Cells(GetRow, GetCol).Value
End Function

Any help much appreciated.

 

[SkipVought]

Public Function GetIt(FieldDetails)
    Application.Volatile
    With FieldDetails
        GetIt = ActiveWorkbook.Sheets("OtherSheet").Cells(.Row, .Column).Value
    End With
End Function

Skip,

_{[red]A palindrome gone wrong?[/red]
A man, a plan, a ROOT canal...
PULLEMALL!}

 

[SkipVought]

BTW,

You do NOT want to use ActiveCell. You will get results that you do not expect!

Skip,

_{[red]A palindrome gone wrong?[/red]
A man, a plan, a ROOT canal...
PULLEMALL!}

 

[PHV]

And if FieldDetails is a string instead of a range:
With Range(FieldDetails)

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[SkipVought]

PHV,

I assumed that he was using a range reference

GetCol = FieldDetails.Column

Skip,

_{[red]A palindrome gone wrong?[/red]
A man, a plan, a ROOT canal...
PULLEMALL!}

 

[PHV]

Yes, but I was unsure (emphasis is mine):

=GetIt(NamedField)

 

[R3D3]

Getting closer but what I want is the .Row of the cell that contains the function rather than that of the Namesfield supplied to the function.

thanks

 

[PHV]

You may play with Application.Caller.Row

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[SkipVought]

What is the range NamedField? how is it defined?

Skip,

_{[red]A palindrome gone wrong?[/red]
A man, a plan, a ROOT canal...
PULLEMALL!}

 

[R3D3]

Application.Caller.Row is what I was looking for.
Works a treat. Your a star.

FYI. Namedfield was one of a collection of Names.