How do I exchange cell contents between two non-adjacent cells?

[dave parta]

There must be a way to simply switch the contents of two Excel cells, what is
it?
answers to (e-mail address removed) will be greatly appreciated.

 

[Bob I]

Not natively.

 

[Mike H]

Hi,

It depeends on your definition od simple. Alt +F11 to open VB editor. Right
click 'This Workbook' and insert module and paste the code below in.

Select you 2 cells then run the code

Sub SwapEm()
Dim Firstvalue(), FirstValueAddress()
a = 1
ReDim Firstvalue(2), FirstValueAddress(2)
For Each c In Selection
Firstvalue(a) = c.Value
FirstValueAddress(a) = c.Address
a = a + 1
Next c
Range(FirstValueAddress(1)).Select
ActiveCell = Firstvalue(2)
Range(FirstValueAddress(2)).Select
ActiveCell = Firstvalue(1)
End Sub

Mike

 

[Gord Dibben]

Here is some code from Gary's Student

Sub swap_um()
Dim v0 As Variant
Dim v1 As Variant
Dim s(2) As String
If Selection.Count <> 2 Then Exit Sub
i = 0
For Each rr In Selection
s(i) = rr.Address
i = i + 1
Next
v0 = Range(s(0)).Value
v1 = Range(s(1)).Value
Range(s(1)).Value = v0
Range(s(0)).Value = v1
End Sub

Select any two cells...........adjacent or non-adjacentt.

Run macro.


Gord Dibben MS Excel MVP