Swap Cells Based On Content

[Nick van Staden]

Looking for an excel VBA that will assist with the following:

if column A has content swap it with content in column B
​

[TABLE="width: 500"]
<tbody>[TR]
[TD][/TD]
[TD]

FPE100​

<strike></strike>
[/TD]
[/TR]
[TR]
[TD]<strike></strike>
[/TD]
[TD]

FPE101​

<strike></strike>
[/TD]
[/TR]
[TR]
[TD]

FPA001​

<strike></strike>
[/TD]
[TD]FPO00111[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]

FPE102​

<strike></strike>
[/TD]
[/TR]
[TR]
[TD]<strike></strike>
[/TD]
[TD]FPE103[/TD]
[/TR]
</tbody>[/TABLE]
<strike></strike>
So the response should be:
[TABLE="width: 500"]
<tbody>[TR]
[TD][/TD]
[TD]

FPE100​

​

[/TD]
[/TR]
[TR]
[TD]<strike></strike><strike></strike>[/TD]
[TD]

FPE101​

​

[/TD]
[/TR]
[TR]
[TD]

FPO00111​

​

<strike></strike>[/TD]
[TD]

FPA001​

​

[/TD]
[/TR]
[TR]
[TD]<strike></strike><strike></strike>[/TD]
[TD]

FPE102​

​

[/TD]
[/TR]
[TR]
[TD]<strike></strike><strike></strike>[/TD]
[TD]

FPE103​

​

<strike></strike>[/TD]
[/TR]
</tbody>[/TABLE]
<strike></strike><strike></strike>

 

[BarryL]

can col B be empty and col A have a value? Can you try the below?

Sub swap()
Dim rng As Range, aStr As String, bStr As String
For Each rng In Range("A1:A" & Range("A" & Rows.Count).End(xlUp).Row)
If rng.Value <> "" Then
aStr = rng.Value
bStr = rng.Offset(, 1).Value
rng.Value = bStr
rng.Offset(, 1).Value = aStr
bStr = ""
aStr = ""
End If
Next
End Sub

 

[Peter_SSs]

Try this with a copy of your workbook.

Sub Swap()
  Dim a As Variant, tmp As Variant
  Dim i As Long
  
  With Range("A1", Range("A" & Rows.Count).End(xlUp)).Resize(, 2)
    a = .Value
    For i = 1 To UBound(a)
      If Len(a(i, 1)) > 0 Then
        tmp = a(i, 1)
        a(i, 1) = a(i, 2)
        a(i, 2) = tmp
      End If
    Next i
    .Value = a
  End With
End Sub

 

[Nick van Staden]

can col B be empty and col A have a value? Can you try the below?

Sub swap()
Dim rng As Range, aStr As String, bStr As String
For Each rng In Range("A1:A" & Range("A" & Rows.Count).End(xlUp).Row)
If rng.Value <> "" Then
aStr = rng.Value
bStr = rng.Offset(, 1).Value
rng.Value = bStr
rng.Offset(, 1).Value = aStr
bStr = ""
aStr = ""
End If
Next
End Sub

Thanks worked 100%