swap values between two cells
 

swap values between two cells
 

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

On Nov 26, 12:01 pm, killdare
wrote:

swap values between two cells
 

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" wrote in message
...
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

On Nov 26, 12:01 pm, killdare
wrote:

swap values between two cells
 

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

On Nov 26, 4:08 pm, "Rick Rothstein \(MVP - VB\)"
wrote:
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" wrote in message

...



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

On Nov 26, 12:01 pm, killdare
wrote:- Hide quoted text -

- Show quoted text -