Jul 22 2022 10:18 PM
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
Jul 22 2022 11:55 PM
SolutionHasValue 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
Jul 22 2022 11:55 PM
SolutionHasValue 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