SOLVED

Swapping Text in a Column with the Right Column

Copper Contributor

Hello, as the title suggest, I'm trying to swap the values in a column with the column on the right but the code I wrote is not working. Any Advice?

Sub SwapTextwithRightColumn()
Dim MyRange As Range
Dim MyCell As Range
Set MyRange = Selection

For Each MyCell In MyRange
If MyCell.HasValue Then

MyCell.Value = MyCell.Offset(0, 1).Value
MyCell.Offset(0, 1).Value = MyCell

End If

Next MyCell

End Sub
1 Reply
best response confirmed by NRASA0743 (Copper Contributor)
Solution

@NRASA0743 

HasValue is not a property of a cell.

And  you cannot switch values this way, for by the time you execute

MyCell.Offset(0, 1).Value = MyCell

the value of MyCell has been changed to that of the cell to the right.

Use this:

Sub SwapTextwithRightColumn()
    Dim MyRange As Range
    Dim MyCell As Range
    Dim MyValue As Variant

    Set MyRange = Selection
    For Each MyCell In MyRange
        If MyCell.Value <> "" Then
            MyValue = MyCell.Value
            MyCell.Value = MyCell.Offset(0, 1).Value
            MyCell.Offset(0, 1).Value = MyValue
        End If
    Next MyCell
End Sub

or

Sub SwapTextwithRightColumn()
    Dim MyRange As Range
    Dim MyCell As Range

    Set MyRange = Selection
    For Each MyCell In MyRange
        If MyCell.Value <> "" Then
            MyCell.Cut
            MyCell.Offset(0, 1).Insert Shift:=xlToRight
        End If
    Next MyCell
End Sub
1 best response

Accepted Solutions
best response confirmed by NRASA0743 (Copper Contributor)
Solution

@NRASA0743 

HasValue is not a property of a cell.

And  you cannot switch values this way, for by the time you execute

MyCell.Offset(0, 1).Value = MyCell

the value of MyCell has been changed to that of the cell to the right.

Use this:

Sub SwapTextwithRightColumn()
    Dim MyRange As Range
    Dim MyCell As Range
    Dim MyValue As Variant

    Set MyRange = Selection
    For Each MyCell In MyRange
        If MyCell.Value <> "" Then
            MyValue = MyCell.Value
            MyCell.Value = MyCell.Offset(0, 1).Value
            MyCell.Offset(0, 1).Value = MyValue
        End If
    Next MyCell
End Sub

or

Sub SwapTextwithRightColumn()
    Dim MyRange As Range
    Dim MyCell As Range

    Set MyRange = Selection
    For Each MyCell In MyRange
        If MyCell.Value <> "" Then
            MyCell.Cut
            MyCell.Offset(0, 1).Insert Shift:=xlToRight
        End If
    Next MyCell
End Sub

View solution in original post