Forum Discussion

NRASA0743's avatar
NRASA0743
Copper Contributor
Jul 23, 2022
Solved

Swapping Text in a Column with the Right Column

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
excel
Macros and VBA
  • HansVogelaar's avatar
    HansVogelaar
    Jul 23, 2022

    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 Reply

Sort By
  • HansVogelaar's avatar
    HansVogelaar
    MVP
    Jul 23, 2022

    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

Resources