Andrew - thank you v much - you hit the nail on th ehead (i did not
know it returned an array!). Piotr, thank you too for your input!
- Chris


Andrew Taylor wrote:
The first function is actually returning an array of the same
dimensions as the input range, with the top right cell of the
input as its top left; so it just happens to display the right
value. To fix the second function you need to make sure
you pick the single cell you're interested in. The line:

topright = values.Offset(0, values.Columns.Count - 1).Cells(1, 1).2

will do it.

Andrew

wrote:
Can anybody please enlighten me why this Excel VBA function* works:

Function topright(values)
topright = values.Offset(0, values.Columns.Count - 1)
End Function

and why this one does not (it alwasy returns a #VALUE! error):

Function topright(values)
topright = values.Offset(0, values.Columns.Count - 1) * 2
End Function

Help, would greatly appreciated before insanity sets in.




* this function, by the way, is supposed to find the top right hand
value in any excel range. So if, in Excel, you did =topright(A1:F5),
the function would return the value in A5.