Can anyone tell me why the first of the following functions works when used in an array formula and the second does not?
Very simple test:
Public Function test(myarray As Variant)
test = myarray
End Function
when i use it in an array formula using {} brackets such as:
{=test(A1:A20)}
it works
BUT, when I try to make the following work I keep getting #VALUE! error:
Public Function ArrayCapFloor(myarray As Variant, floor As Long, cap As Long)
For n = LBound(myarray) To UBound(myarray)
If myarray(n) < floor Then ' replace value below floor with floor
myarray(n) = floor
End If
If myarray(n) > cap Then ' replace value above cap with cap
myarray(n) = cap
End If
ArrayCapFloor = myarray
Next n
End Function
I know the function is working, but cannot get it to work as part of an array formula.
I have been trying the following but keeps giving me an error.
{=+ArrayCapFloor(A1:A20,33,107)}
Am I missing something????
Thanks help.
Bookmarks