Try this:

function Diag(adrs as string) as string
diag = Range(adrs).end(xltoleft).address
end function

The input here is a string, eg "D7" and the result is a string containing
the address of the cell it finds.


" wrote:

this is a 2 part because i dont think the first part is possible

1) i'd like to have a function that has no arguments but references
the cell that the formula for the function is being input into and
just takes the value of the first non empty cell to the left (i dont
think its possible to reference the cell with the formula though???)

2) because of this i wrote the following passing one range variable
but i get a #value error, i know it has to do with the passing of the
range because if i hard code a cell it works fine for that row.

the code is:

Function diag(rng)
diag = Range("rng").End(xlToLeft)
End Function


any ideas????

thanks