Thread: exchange contents of cells

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

--
Message posted via http://www.officekb.com

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
--
Rgds
Max
xl 97
---
GMT+8, 1° 22' N 103° 45' E
xdemechanik <at>yahoo<dot>com
----
""jeremy via OfficeKB.com"" <forum@OfficeKB.com> wrote in message
news:50EBEE37ADED3@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
>
> --
> Message posted via http://www.officekb.com

Max

you lost me already--I'm green.

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

jeremy

--
Message posted via http://www.officekb.com

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

--
Message posted via http://www.officekb.com

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

jeremy

--
Message posted via http://www.officekb.com

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

--
Rgds
Max
xl 97
---
GMT+8, 1° 22' N 103° 45' E
xdemechanik <at>yahoo<dot>com
----
"jeremy" <forum@OfficeKB.com> wrote in message
news:50F7AC89BA650@OfficeKB.com...
> Max
> I got the little box witht the pasted commands in it, but can't get it to
> work....
>
> jeremy
>
> --
> Message posted via http://www.officekb.com

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

--
Rgds
Max
xl 97
---
GMT+8, 1° 22' N 103° 45' E
xdemechanik <at>yahoo<dot>com
----