swap values between two cells

[carlo]

You could have written a little bit more.

if you want to do it in vba use this function

function swap(cell1 as range, cell2 as range)

dim Temp_val as variant
Temp_val = cell1.value
cell1.value = cell2.value
cell2.value = Temp_val

end function

hth

Carlo

 

[Rick Rothstein \(MVP - VB\)]

Since you are not going to return a value from your function, it probably
should be a Sub instead.

While the Temp variable method is probably the fastest, I thought the
readers of this thread might find a solution that does not require a
temporary variable of some interest.

If the two cells contain numerical values...

Sub Swap(C1 As Range, C2 As Range)
C1.Value = C1.Value + C2.Value
C2.Value = C1.Value - C2.Value
C1.Value = C1.Value - C2.Value
End Sub

If the two cells contain numerical and/or text values...

Sub Swap(C1 As Range, C2 As Range)
C1.Value = C1.Value & C2.Value
C2.Value = Replace(C1.Value, C2.Value, "")
C1.Value = Replace(C1.Value, C2.Value, "")
End Sub

Rick

 

[carlo]

Thanks for the additional info.

yeah you're right, should have taken a sub,
don't know why I didn't.

nice approach without a tempvar.

cheers

Carlo