Barryj
09-13-2020, 10:01 PM
I have the below code which works fine but is a bit slow, just wondering if it could be made more efficient to speed up the process.
Sub Offset()Application.ScreenUpdating = False
Dim r As Range
Dim a As Range
Set r = ActiveSheet.Range("C5:C1372")
For Each a In r
If a.Value = "WIC" Then
a.Offset(, -1) = a.Value
a.Value = ""
End If
If a.Value = "WTC" Then
a.Offset(, -1) = a.Value
a.Value = ""
End If
If a.Value = "AL" Then
a.Offset(, -1) = a.Value
a.Value = ""
End If
If a.Value = "CDW" Then
a.Offset(, -1) = a.Value
a.Value = ""
End If
If a.Value = "INJ" Then
a.Offset(, -1) = a.Value
a.Value = ""
End If
If a.Value = "Sick" Then
a.Offset(, -1) = a.Value
a.Value = ""
End If
If a.Value = "CL" Then
a.Offset(, -1) = a.Value
a.Value = ""
End If
If a.Value = "TW" Then
a.Offset(, -1) = a.Value
a.Value = ""
End If
If a.Value = "JC" Then
a.Offset(, -1) = a.Value
a.Value = ""
End If
If a.Value = "LSL" Then
a.Offset(, -1) = a.Value
a.Value = ""
End If
If a.Value = "WO" Then
a.Offset(, -1) = a.Value
a.Value = ""
End If
If a.Value = "2PJ" Then
a.Offset(, -1) = a.Value
a.Value = ""
End If
If a.Value = "AJQ" Then
a.Offset(, -1) = a.Value
a.Value = ""
End If
If a.Value = "HFG" Then
a.Offset(, -1) = a.Value
a.Value = ""
End If
Next
LR = Cells(Rows.Count, 17).End(xlUp).Row
Application.ScreenUpdating = True
End Sub
Thanks for any assistance.
Sub Offset()Application.ScreenUpdating = False
Dim r As Range
Dim a As Range
Set r = ActiveSheet.Range("C5:C1372")
For Each a In r
If a.Value = "WIC" Then
a.Offset(, -1) = a.Value
a.Value = ""
End If
If a.Value = "WTC" Then
a.Offset(, -1) = a.Value
a.Value = ""
End If
If a.Value = "AL" Then
a.Offset(, -1) = a.Value
a.Value = ""
End If
If a.Value = "CDW" Then
a.Offset(, -1) = a.Value
a.Value = ""
End If
If a.Value = "INJ" Then
a.Offset(, -1) = a.Value
a.Value = ""
End If
If a.Value = "Sick" Then
a.Offset(, -1) = a.Value
a.Value = ""
End If
If a.Value = "CL" Then
a.Offset(, -1) = a.Value
a.Value = ""
End If
If a.Value = "TW" Then
a.Offset(, -1) = a.Value
a.Value = ""
End If
If a.Value = "JC" Then
a.Offset(, -1) = a.Value
a.Value = ""
End If
If a.Value = "LSL" Then
a.Offset(, -1) = a.Value
a.Value = ""
End If
If a.Value = "WO" Then
a.Offset(, -1) = a.Value
a.Value = ""
End If
If a.Value = "2PJ" Then
a.Offset(, -1) = a.Value
a.Value = ""
End If
If a.Value = "AJQ" Then
a.Offset(, -1) = a.Value
a.Value = ""
End If
If a.Value = "HFG" Then
a.Offset(, -1) = a.Value
a.Value = ""
End If
Next
LR = Cells(Rows.Count, 17).End(xlUp).Row
Application.ScreenUpdating = True
End Sub
Thanks for any assistance.