Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
\jeremy via OfficeKB.com\
 
Posts: n/a
Default 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
  #2   Report Post  
Max
 
Posts: n/a
Default

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 <atyahoo<dotcom
----
""jeremy via OfficeKB.com"" wrote in message
...
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



  #3   Report Post  
jeremy
 
Posts: n/a
Default

Max

you lost me already--I'm green.

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

jeremy

--
Message posted via http://www.officekb.com
  #4   Report Post  
jeremy
 
Posts: n/a
Default

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

--
Message posted via http://www.officekb.com
  #5   Report Post  
jeremy
 
Posts: n/a
Default

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


  #6   Report Post  
Max
 
Posts: n/a
Default

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 <atyahoo<dotcom
----
"jeremy" wrote in message
...
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



  #7   Report Post  
Max
 
Posts: n/a
Default

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 <atyahoo<dotcom
----


Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Counting empty cells within a range of cells Rosehill - ExcelForums.com New Users to Excel 2 May 2nd 05 08:53 AM
Counting empty cells within a range of cells Rosehill - ExcelForums.com New Users to Excel 0 April 7th 05 12:47 AM
Adding contents of one cell to a range of cells. CLJinVA Excel Worksheet Functions 1 February 10th 05 11:19 PM
copy group of cells to another group of cells using "IF" in third Chuckak Excel Worksheet Functions 2 November 10th 04 07:04 PM
Modify Row & Cell Contents based upon Cells Values bpat1434 Excel Worksheet Functions 0 November 7th 04 04:31 PM


All times are GMT +1. The time now is 06:53 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"