passing an argument
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
|