Function diag() As Variant
Application.Volatile
diag = Application.Caller.End(xlToLeft).Value
End Function

Jim Cone
San Francisco, USA
http://www.realezsites.com/bus/primitivesoftware
(Excel Add-ins / Excel Programming)




wrote in message
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