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

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

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

Function nextleft() As Variant
nextleft = ""
i = Application.Caller.Row
j = Application.Caller.Column - 1
For k = 1 To j
If Cells(i, k).Value = "" Then
Else
nextleft = Cells(i, k).Value
End If
Next
End Function

will do what you want, but it is not volatile. It is better to use args.
They will force re-calculation was values are added or removed.
--
Gary''s Student - gsnu2007c


" 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