exchange data in cells

barns

New Member
Joined
Jan 16, 2003
Messages
5
Hello!

Is there a command / tool in Excel wherein you can interchange data from two cells? To illustrate..

Cell A1 contains value X.
Cell B1 contains value Y.

What quick command would make it easy to interchange the two so that..

Cell A1 would contain value Y.
Cell B1 would contain value X.

Any help would be greatly appreciated!

Barns
 

Excel Facts

Which lookup functions find a value equal or greater than the lookup value?
MATCH uses -1 to find larger value (lookup table must be sorted ZA). XLOOKUP uses 1 to find values greater and does not need to be sorted.
Hi Barns, welcome to the board.
How's this grab ya?
Code:
Sub DoTheSwap()
i = [A1].Value
j = [B1].Value
[A1] = j
[B1] = i
End Sub
Dan
 
Upvote 0
Hi Dan,

Macro Works fine, but I'm limited to A1 and B1 cells only.

Is there a way wherein I can just highlight any two cells and run a command to do the swap? (non-macro way would be better although not really necessary)

Thanks!

Barns
 
Upvote 0
I don't know of a way to do this with a formula, but then I suck at formulas.
I also can't do that with 2 cells selected in VBA, but I can do it with one cell selected and the other always being a specified distance from the selection (ie, 1 to the right, or 7 below (or 5 rows above & 2 columns to the left), something like that, as long as it's constant. Will something like that do ya?
 
Upvote 0
Hi there

Here is another variation if you do not want to hardwire the cells.
Before running the macro you must select 2 separate cells using Control.

Sub SwapSelection()
On Error GoTo Out
If Selection.Cells.Count <> 2 Then Exit Sub
Dim str, str1, str2 As String
str = Selection.Address(rowabsolute, columnabsolute)
str1 = Left(str, WorksheetFunction.Find(",", str) - 1)
str2 = Mid(str, WorksheetFunction.Find(",", str) + 1, 7)

x = Range(str1).Value
y = Range(str2).Value

Range(str1).Value = y
Range(str2).Value = x
Out:
End Sub


MERGED CELLS. If merged cells are involved remove this line from the code:
If Selection.Cells.Count <> 2 Then Exit Sub

regards
Derek
 
Upvote 0
I've got it!

Thanks for all your help, although the solutions provided are quite complicated. (i've no knowledge in VBA) I really appreciate it!

Barns
 
Upvote 0

Forum statistics

Threads
1,213,536
Messages
6,114,215
Members
448,554
Latest member
Gleisner2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top