exchange contents of cells

[\jeremy via OfficeKB.com\]

If i have 2 cells (or groups of cells) is there a way to exchange the
contents or do I have to copy/paste the first contents into empty cell, then
copy/paste second contents into first cell, etc, etc....

Thanks.

Jeremy

 

[Max]

As a start, here's some code to play with,
which swaps the values in 2 cells, A1 and A2

Steps
----
Draw a command button on the sheet from the Control Toolbox toolbar
Double click on the button to go to VBE
Copy and paste the code below into the code window on the right
(Clear the defaults first)

Private Sub CommandButton1_Click()
Dim x As Integer
x = Range("a1").Value
Range("a1").Value = Range("a2").Value
Range("a2").Value = x
End Sub

Press Alt+Q to get back to Excel
Click the "Design Mode" icon on the control toolbox toolbar
to "Exit Design Mode" (The icon should be "un-depressed")

Test it out !
Enter 2 different numbers into A1 and A2
Click the button, and the 2 numbers will be swapped
Click the button again to swap back. And so on ..

Do hang around awhile for other better answers / examples

 

[jeremy]

Max

you lost me already--I'm green.

Control Toolbox toolbar? couldn't find it....

jeremy

 

[jeremy]

Oops, I lied--I found it...

 

[jeremy]

Max
I got the little box witht the pasted commands in it, but can't get it to
work....

jeremy

 

[Max]

Perhaps try this sample file, with the implemented construct:
http://www.savefile.com/files/4503586
File: jeremy_newusers.xls
(Note: You need to "Enable macros")

 

[Max]

I got the little box witht the pasted commands in it,

but can't get it to work....

Are you stuck at the VBE part, i.e. step 3 below ? I don't know <g>

Or, maybe at step 5 below ? We need to click to "un-depress" the Design Mode
icon (that's the one with the triangle/pencil/ruler graphic) to exit design
mode, otherwise we won't be able to test/click the command button

Steps
----
1. Draw a command button on the sheet from the Control Toolbox toolbar
2. Double click on the button to go to VBE
3. Copy and paste the code below into the code window on the right
(Clear the defaults first)

Private Sub CommandButton1_Click()
Dim x As Integer
x = Range("a1").Value
Range("a1").Value = Range("a2").Value
Range("a2").Value = x
End Sub

4. Press Alt+Q to get back to Excel
5. Click the "Design Mode" icon on the control toolbox toolbar
to "Exit Design Mode" (The icon should be "un-depressed")