exchange contents of cells

  • Thread starter Thread starter \jeremy via OfficeKB.com\
  • Start date Start date
J

\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
 
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
 
Max

you lost me already--I'm green.

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

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

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

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")
 
Back
Top