Is there an easy way to swap the contents of two cells in Excel?
 

I think the subject says it all...

?B?Ti5CLiBZb25k?= <N.B. wrote in
:

I think the subject says it all...


You could add a button to the sheet with following code behind it:

Private Sub CommandButton1_Click()
cel1 = InputBox("First cell?")
cel2 = InputBox("Second cell?")
c1 = Range(cel1).Value
c2 = Range(cel2).Value
Range(cel1).Value = c2
Range(cel2).Value = c1
End Sub

You will be asked for the 2 cell addresses and the contents will be
swapped.

manually???

Edit|copy one cell
select a helper cell and edit|paste special|values

edit|copy the second cell
select the first cell
edit|paste special|values

select the helper cell
edit|copy
select the second cell
edit|paste special|Values

clean up that helper cell.

N.B. Yond wrote:

I think the subject says it all...

--

Dave Peterson

N. B.,

You'd like to select the two cells, click the Swap Button, and presto,
they're swapped. Unfortunately, there ain't such a button.

A macro could do it.. Then there could be a swap button. Or a keyboard
shortcut. Or a menu item. If you're interested, post back. We'll write it
for you. What do you want to do with formatting? Leave in the original
locations? Or swap that too? What? Should formula cell references to
these cells get swapped, or remain with the original locations?
--
Earl Kiosterud
www.smokeylake.com

"N.B. Yond" <N.B. wrote in message
...
I think the subject says it all...

Select cell'A', Cut.
Drag cell 'B' to cell 'A', click OK to replace content.
Click cell 'B', Paste.

HTH |:)


On 7/23/05 8:07 AM, in article
, "N.B. Yond" <N.B.
wrote:

I think the subject says it all...

Is there an easy way to swap the contents of two cells in Exce
 

I'm missing something; when I replace A in step 2, the clipboard becomes
empty so step 3 is not possible.

"CyberTaz" wrote:

Select cell'A', Cut.
Drag cell 'B' to cell 'A', click OK to replace content.
Click cell 'B', Paste.

HTH |:)


On 7/23/05 8:07 AM, in article
, "N.B. Yond" <N.B.
wrote:

I think the subject says it all...

Is there an easy way to swap the contents of two cells in Exce
 

Hello Earl Kiosterud,

My name is Sumayah and I would like to know how can I exchange/swap the
content of two cells or more?
Appreciating your reply.
Regards.
Sumayah.


"Earl Kiosterud" wrote:

N. B.,

You'd like to select the two cells, click the Swap Button, and presto,
they're swapped. Unfortunately, there ain't such a button.

A macro could do it.. Then there could be a swap button. Or a keyboard
shortcut. Or a menu item. If you're interested, post back. We'll write it
for you. What do you want to do with formatting? Leave in the original
locations? Or swap that too? What? Should formula cell references to
these cells get swapped, or remain with the original locations?
--
Earl Kiosterud
www.smokeylake.com

"N.B. Yond" <N.B. wrote in message
...
I think the subject says it all...

Is there an easy way to swap the contents of two cells in Exce
 

I am not Earl, but maybe this will do...
--
Jim Cone
San Francisco, USA
http://www.realezsites.com/bus/primitivesoftware

Sub SwitchCellFormulas()
'July 07, 2006 - James Cone - San Francisco, USA
On Error GoTo SwapError
Dim rngSelect As Excel.Range
Dim rngOne As Excel.Range
Dim rngTwo As Excel.Range
Dim lngNum As Long
Dim strGeneric As String
Dim varValueOne As Variant
Set rngSelect = Excel.Selection
'If entire columns or rows selected, don't use, resize to used range boundries.
If rngSelect.Rows.Count = ActiveSheet.Rows.Count Then
Set rngSelect = Application.Intersect(rngSelect, ActiveSheet.UsedRange.EntireRow)
ElseIf rngSelect.Columns.Count = ActiveSheet.Columns.Count Then
Set rngSelect = Application.Intersect(rngSelect, ActiveSheet.UsedRange.EntireColumn)
End If
'Check for merged cells and the number of areas/cells selected.
If rngSelect.MergeCells Or IsNull(rngSelect.MergeCells) Then
strGeneric = "Unmerge cells in the selection and try again. "
ElseIf rngSelect.Areas.Count 2 Then
strGeneric = "Can only swap two selections." & vbCr & _
"There are " & rngSelect.Areas.Count & " selections on the worksheet. "
ElseIf rngSelect.Areas.Count = 1 Then
If rngSelect.Columns.Count = 2 Then
Set rngOne = rngSelect.Columns(1).Cells
Set rngTwo = rngSelect.Columns(2).Cells
ElseIf rngSelect.Rows.Count = 2 Then
Set rngOne = rngSelect.Rows(1).Cells
Set rngTwo = rngSelect.Rows(2).Cells
ElseIf rngSelect.Count < 2 Then
strGeneric = " Two selections are necessary. "
ElseIf Application.CountA(rngSelect) = 0 Then
strGeneric = "The selection is blank. "
Else
Set rngOne = rngSelect(1)
Set rngTwo = rngSelect(2)
End If
Else 'Two areas
Set rngOne = rngSelect.Areas(1)
Set rngTwo = rngSelect.Areas(2)
If rngOne.Rows.Count < rngTwo.Rows.Count Or _
rngOne.Columns.Count < rngTwo.Columns.Count Then
strGeneric = "The two selections must be the same size. "
ElseIf Application.CountA(rngOne) + Application.CountA(rngTwo) = 0 Then
strGeneric = "Both selections are blank. "
End If
End If
If Len(strGeneric) Then
MsgBox strGeneric, vbInformation, " Swap Cells"
GoTo CleanUp
ElseIf rngOne.Address = rngTwo.Address Then
GoTo CleanUp
End If
'With multiple cells Apply formats and formulas to each cell.
If rngOne.Count 1 Then
Application.ScreenUpdating = False
For lngNum = 1 To rngOne.Count
Set rngSelect = rngOne(lngNum)
With rngSelect
strGeneric = .NumberFormat
varValueOne = .Formula
.NumberFormat = rngTwo(lngNum).NumberFormat
.Formula = rngTwo(lngNum).Formula
End With
rngTwo(lngNum).NumberFormat = strGeneric
rngTwo(lngNum).Formula = varValueOne
Next
Application.ScreenUpdating = True
Else 'One cell vs. one cell
strGeneric = rngOne.NumberFormat
varValueOne = rngOne.Formula
rngOne.NumberFormat = rngTwo.NumberFormat
rngOne.Formula = rngTwo.Formula
rngTwo.NumberFormat = strGeneric
rngTwo.Formula = varValueOne
End If
CleanUp:
On Error Resume Next
Set rngSelect = Nothing
Set rngOne = Nothing
Set rngTwo = Nothing
Exit Sub
SwapError:
Application.ScreenUpdating = True
MsgBox "Error " & Err.Number & " - " & Err.Description & " ", vbCritical, " Swap Cells"
GoTo CleanUp
End Sub
'-------------

"sumtyb"

wrote in message
Hello Earl Kiosterud,
My name is Sumayah and I would like to know how can I exchange/swap the
content of two cells or more?
Appreciating your reply.
Regards.
Sumayah.


"Earl Kiosterud" wrote:
N. B.,

You'd like to select the two cells, click the Swap Button, and presto,
they're swapped. Unfortunately, there ain't such a button.

A macro could do it.. Then there could be a swap button. Or a keyboard
shortcut. Or a menu item. If you're interested, post back. We'll write it
for you. What do you want to do with formatting? Leave in the original
locations? Or swap that too? What? Should formula cell references to
these cells get swapped, or remain with the original locations?
--
Earl Kiosterud
www.smokeylake.com

"N.B. Yond" <N.B. wrote in message
...
I think the subject says it all...