How to constract an array UDF?
I'm not sure why you would want a UDF to do what you already have a relatively compact formula doing, but here it is...
Function REVCOL(R As Range) As Variant
Dim CellValues As Variant, CallerRow As Long
CellValues = WorksheetFunction.Transpose(R)
CallerRow = Application.Caller.Row
REVCOL = CellValues(R.Count + R(1).Row - CallerRow)
End Function
Note that this is **not** an array formula... just commit it with the Enter key; and don't forget to make the range argument for it with absolute cell references so that when you copy the function down, the cell address doesn't "drift".
--
Rick (MVP - Excel)
"Faraz A. Qureshi" wrote in message ...
Sure would oblige if one would kindly provide the CODE for developing a UDF
for simple formula of reversing the column values like the following:
=OFFSET($A$1,ROWS($A$1:$A$20)-ROWS($B$1:B1),0)
Thus, if a UDF like the following, is entered in an array form:
{=REVCOL(A1:A20)}
on the range B1:B20, the B1:B20 would represent the data of A1:A20 but in
the reverse order.
Thanx in advance,
Best Regards,
Faraz
|