Swapping values between two cells

[Bass71]

Is there a function that succintly swaps the values of two cells. For example:

cell D1 has a value of 34 and E1 has a value of 50...all I want to do is swap those two cell values, such that E1 now equals 34 and D1 equals 50.

I've got code that, when a condition is met, cuts from one, then temporarily pastes it in an empty column and then cuts and pastes the other and so on...but that's way too cumbersome.

 

[MrsMcGuyver]

I don't know of any specific functions to do this but try holding onto the value of one of the fields in a local variable rather than writing it into a blank cell. Almost as cumbersome but it will be one less write.

 

[AdaHacker]

I'll second that. Cutting and pasting is just a really ugly way to do it. If you're using Excel automation, the best way to do it would be with a temporary variable and the Range.Value property. A quick and dirty example might be

Sub Swap(Byref Rng1 As Excel.Range, Byref Rng2 As Excel.Range)
Dim temp As Variant
temp = Rng1.Value
Rng1.Value = Rng2.Value
Rng2.Value = temp
End Sub

 

[strongm]

Here's some VBA:
[tt]
' Note routine only does rough checking for whether onlt two cells are selected
' Procedure swaps the contents of two cells in the current selection
Public Sub SwapContents()
Dim rngSwapRange As Range
Set rngSwapRange = Selection

If rngSwapRange.Areas.Count = 2 Then ' Cells not next to each other?
If rngSwapRange.Areas(1).Count = 1 And rngSwapRange.Areas(2).Count = 1 Then ' Quick and dirty check to ensure just two cells selected
tempvalue = rngSwapRange.Areas(1).Value
rngSwapRange.Areas(1).Value = rngSwapRange.Areas(2).Value
rngSwapRange.Areas(2).Value = tempvalue
End If
ElseIf rngSwapRange.Areas.Count = 1 Then ' Cells not next to each other?
If rngSwapRange.Areas(1).Count = 2 Then ' Quick and dirty check for just two cells
tempvalue = rngSwapRange(1).Value
rngSwapRange(1).Value = rngSwapRange(2).Value
rngSwapRange(2).Value = tempvalue
End If
End If
End Sub[/tt]