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

N.B. Yond · External Since: Jul 23, 2005 Posts: 1

I think the subject says it all...

Dave Peterson · External Since: Jul 08, 2005 Posts: 16134

Earl Kiosterud · External Since: Jul 12, 2005 Posts: 997

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. Yond.DeleteThis@discussions.microsoft.com> wrote in message
news:4C32CCD5-DA6F-43A8-9502-B2348D183371@microsoft.com...
>I think the subject says it all...

Dodo · External Since: Jul 06, 2005 Posts: 159

=?Utf-8?B?Ti5CLiBZb25k?= <N.B. Yond.RemoveThis@discussions.microsoft.com> wrote in
news:4C32CCD5-DA6F-43A8-9502-B2348D183371@microsoft.com:

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

CyberTaz · External Since: Jul 20, 2005 Posts: 663

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
4C32CCD5-DA6F-43A8-9502-B2348D183371.RemoveThis@microsoft.com, "N.B. Yond" <N.B.
Yond.RemoveThis@discussions.microsoft.com> wrote:

> I think the subject says it all...

Dave · External Since: Mar 14, 2006 Posts: 390

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
> 4C32CCD5-DA6F-43A8-9502-B2348D183371.TakeThisOut@microsoft.com, "N.B. Yond" <N.B.
> Yond.TakeThisOut@discussions.microsoft.com> wrote:
>
> > I think the subject says it all...
>
>

sumtyb · External Since: Sep 23, 2006 Posts: 1

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. Yond DeleteThis @discussions.microsoft.com> wrote in message
> news:4C32CCD5-DA6F-43A8-9502-B2348D183371@microsoft.com...
> >I think the subject says it all...
>
>
>

Jim Cone · External Since: Dec 11, 2003 Posts: 834

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"
<sumtyb RemoveThis @discussions.microsoft.com>
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. Yond RemoveThis @discussions.microsoft.com> wrote in message
> news:4C32CCD5-DA6F-43A8-9502-B2348D183371@microsoft.com...
> >I think the subject says it all...
>
>
>

jess916 · Joined: May 29, 2008 Posts: 1

The code to swap the cells works great but I need the colors of the cells to swap as well. Any way to accomplish that without rerunning the macro that color coded the cells in the first place?