Change Values in Range Parameter

[vthokienj]

I have a worksheet function that takes two range variables as parameters, each with 5 cells. I want to switch the values such that Xi(1) = Xi(5)...Xi(5) = Xi(1) and do the same for the Yi values. I can not seem to set the variable correctly and it crashes as indicated below. Below is the image of the Xi variable in the watch window which is why I think the Xi.Value2.Value2(1).Value2(1, 1) should work.

The function is called from the worksheet like:
=ChangeValues(D$9:D$13,E$9:E$13)

Thanks for any help.

function ChangeValues(Xi as Range, Yi as Range)

    ' capture the original values - this part gets the correct values
    Dim x1 As Double: x1 = Xi(1)
    Dim x2 As Double: x2 = Xi(2)
    Dim x3 As Double: x3 = Xi(3)
    Dim x4 As Double: x4 = Xi(4)
    Dim x5 As Double: x5 = Xi(5)
    
    Dim y1 As Double: y1 = Yi(1)
    Dim y2 As Double: y2 = Yi(2)
    Dim y3 As Double: y3 = Yi(3)
    Dim y4 As Double: y4 = Yi(4)
    Dim y5 As Double: y5 = Yi(5)

    ' crashes when I try any of these four lines
    Xi(1) = x5
    Xi.Value2(1) = x5
    Xi.Value2.Value2(1) = x5
    Xi.Value2.Value2(1).Value2(1, 1) = x5

End Function

-- removed inline image ---

Watch : - : Xi : : Range/Range : calculations.ValuePL
- : Value2 : : Variant/Variant(1 to 5, 1 to 1) : calculations.ValuePL
- : Value2(1) : : Variant(1 to 1) : calculations.ValuePL
: Value2(1,1) : 5 : Variant/Double : calculations.ValuePL
- : Value2(2) : : Variant(1 to 1) : calculations.ValuePL
: Value2(2,1) : 4 : Variant/Double : calculations.ValuePL
- : Value2(3) : : Variant(1 to 1) : calculations.ValuePL
: Value2(3,1) : 3 : Variant/Double : calculations.ValuePL
- : Value2(4) : : Variant(1 to 1) : calculations.ValuePL
: Value2(4,1) : 2 : Variant/Double : calculations.ValuePL
- : Value2(5) : : Variant(1 to 1) : calculations.ValuePL
: Value2(5,1) : 1 : Variant/Double : calculations.ValuePL

 

[Richard U]

Try xi(1).value = x5

 

[vthokienj]

Thanks, but that does not work.

 

[Peter_SSs]

If you are actually trying to swap the end two values in the worksheet cells, then you will not be able to do that with a function.
See here for more information.

If that is not what you are trying to do, please try to explain again and give example(s)

 

[vthokienj]

Thank you Peter, that was the issue that I was having.