# exchange data in cells

This is a discussion on exchange data in cells within the Excel Questions forums, part of the Question Forums category; Hello! Is there a command / tool in Excel wherein you can interchange data from two cells? To illustrate.. Cell ...

1. ## exchange data in cells

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

2. ## Re: exchange data in cells

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

3. ## Re: exchange data in cells

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

4. ## Re: exchange data in cells

Hi:

Have a look at this nice approach from Yogi...

http://www.mrexcel.com/board2/viewto...ht=swap+values

plettieri

5. ## Re: exchange data in cells

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?

6. ## Re: exchange data in cells

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

7. 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

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•