switch 2 cells around

Is there a way to switch two cells around? Copy something from cell A to
cell B and from B to A without using any extra empty cells. Maybe using
some function?

Thanks,


---
Message posted from http://www.ExcelForum.com/

Hi!

Not to my knowledge.
VBA could do it: sample swap of ranges below:

Sub Swap(a As Range, b As Range)
If a.Count b.Count Then Exit Sub
If a.Rows.Count b.Rows.Count Then Exit Sub

Dim c As Variant
c = a.Formula
a.Formula = b.Formula
b.Formula = c

End Sub

Could be called by a commandbutton which connected to a sub like

Private Sub CommandButton1_Click()
Swap Range("E4:E7"), Range("F4:F7")
End Sub

In this configuration, it would of course toggle the two ranges.

Alf


---
Message posted from http://www.ExcelForum.com/

wow, ok thank you, i'll try that


---
Message posted from http://www.ExcelForum.com/