Custom Function Question

[jbrouse]

Good morning.

I have created a custom function that is used in many columns. What I need the function to do is reference the value in row 1 of whatever column is calling the function. Can anyone tell me how to pull the cell information that is calling the formula?

So right now, in cell D8, I have the formula =FG($A8,$D8), and I want it to also use the value in D1. I could add it as a 3rd variable, but I also want to be able to use the exact same function in cell E8, but have it pull the value from E1.

Is there any way to do this?

Thanks in advance

 

[Weaver]

use D$1 as a reference, then the column but not the row will change as you drag the formula across and down.

eg

=FG($A8,$D8,D$1)

You'll have to change your function accordingly.

You could build it into the function but this way's more consistent.

 

[Richard Schollar]

Hi

Application.Caller will return the cell that contains this function. So you can use the following to pull back the first cell in the column:

Dim r As Range

Set r = Application.Caller.EntireColumn.Cells(1,1)

'do something:

Msgbox "First cell in column calling is " & r.Address

 

[wigi]

Use ThisCell:

As an example:

Function FG() As String
    FG = "A" & Application.ThisCell.Row
End Function

 

[Jonmo1]

I would add it as a 3rd variable in the function, then write it like this in D8

=FG($A8,$D8,D$1)

The D will incriment to E, then F as you fill/copy the formula to the right.

 

[RoryA]

If you use Application.Caller or ThisCell then you will need to make your function volatile, which is inefficient and unnecessary. I would agree you should add a third argument and pass the cell you need.

 

[wigi]

I would agree you should add a third argument and pass the cell you need.

Me too, but I think that if you write UDF's, you are also aware of absolute and relative cell references... But you're right, sure.

 

[RoryA]

No idea what you mean by that, I'm afraid. The OP clearly does know about relative vs. absolute references judging by the original formula in use...

 

[wigi]

Sorry, I misread, ignore my previous comment.