How about this?
Option Explicit
Sub macro1()
Dim i, lrow
lrow = 14 'not sure what this should be
With Sheets(1) 'not sure what this should be.
Fand i = 7 To lrow
If .Range("I" & i).Value Like "BPS*" Then
If Not Range("K" & i) = 0 And Not Range("K" & i) = 1 And Not Range("K" & i) = 2 And Not Range("K" & i) = 3 And Not Range("K7") = 4 And Not Range("K" & i) = 5 And Not Range("K" & i) = 6 And Not Range("K" & i) = 7 And Not Range("K" & i) = 8 And Not Range("K" & i) = 9 And Not Range("K" & i) = 10 And Not Range("K" & i) = 11 And Not Range("K" & i) = 12 And Not Range("K" & i) = 13 And Not Range("K" & i) = 14 Then
Range("K" & i) = 0
'And when you have done it you want to go down to the next row
Selection.Offset(1, 0).Select
End If
ElseIf .Range("I" & i).Value Like "CAH*" Then
If Not Range("K" & i) = 0 And Not Range("K" & i) = 6 And Not Range("K" & i) = 7 And Not Range("K" & i) = 8 And Not Range("K7") = 9 And Not Range("K" & i) = 10 And Not Range("K" & i) = 11 And Not Range("K" & i) = 12 And Not Range("K" & i) = 13 And Not Range("K" & i) = 14 Then
Range("K" & i) = 0
'And when you have done it you want to go down to the next row
Selection.Offset(1, 0).Select
End If
ElseIf .Range("I" & i).Value Like "CAM*" Then
If Not Range("K" & i) = 0 And Not Range("K" & i) = 1 And Not Range("K" & i) = 2 And Not Range("K" & i) = 3 And Not Range("K7") = 4 And Not Range("K" & i) = 5 And Not Range("K" & i) = 6 And Not Range("K" & i) = 7 And Not Range("K" & i) = 8 And Not Range("K" & i) = 9 And Not Range("K" & i) = 10 And Not Range("K" & i) = 11 Then
'And when you have done it you want to go down to the next row
Selection.Offset(1, 0).Select
End If
ElseIf .Range("I" & i).Value Like "CAT*" Then
If Not Range("K7") = 0 And Not Range("K" & i) = 1 And Not Range("K" & i) = 2 And Not Range("K7") = 3 And Not Range("K7") = 4 And Not Range("K" & i) = 5 And Not Range("K" & i) = 6 And Not Range("K" & i) = 7 And Not Range("K" & i) = 8 And Not Range("K" & i) = 9 And Not Range("K" & i) = 10 And Not Range("K" & i) = 11 And Not Range("K" & i) = 12 And Not Range("K" & i) = 13 Then
Range("K" & i) = 0
'And when you have done it you want to go down to the next row
Selection.Offset(1, 0).Select
End If
ElseIf .Range("I" & i).Value Like "SCI*" Then
If Not Range("K" & i) = 0 And Not Range("K7") = 4 And Not Range("K" & i) = 5 And Not Range("K" & i) = 6 And Not Range("K" & i) = 7 And Not Range("K" & i) = 8 And Not Range("K" & i) = 9 And Not Range("K" & i) = 10 And Not Range("K" & i) = 11 And Not Range("K" & i) = 12 And Not Range("K" & i) = 13 And Not Range("K" & i) = 14 Then
Range("K" & i) = 0
'And when you have done it you want to go down to the next row
Selection.Offset(1, 0).Select
End If
ElseIf .Range("I" & i).Value Like "SUS*" Then
If Not Range("K" & i) = 0 And Not Range("K" & i) = 1 And Not Range("K" & i) = 2 And Not Range("K" & i) = 3 And Not Range("K7") = 4 And Not Range("K" & i) = 5 And Not Range("K" & i) = 6 And Not Range("K" & i) = 7 And Not Range("K" & i) = 8 And Not Range("K" & i) = 9 And Not Range("K" & i) = 10 And Not Range("K" & i) = 11 And Not Range("K" & i) = 12 And Not Range("K" & i) = 13 And Not Range("K" & i) = 14 Then
Range("K" & i) = 0
'And when you have done it you want to go down to the next row
Selection.Offset(1, 0).Select
End If
ElseIf .Range("I" & i).Value Like "SPD*" Then
If Not Range("K" & i) = 0 And Not Range("K" & i) = 8 And Not Range("K" & i) = 9 And Not Range("K" & i) = 10 And Not Range("K" & i) = 11 And Not Range("K" & i) = 12 Then
Range("K" & i) = 0
'And when you have done it you want to go down to the next row
Selection.Offset(1, 0).Select
End If
ElseIf .Range("I" & i).Value Like "TAL*" Then
If Not Range("K" & i) = 0 And Not Range("K" & i) = 5 And Not Range("K" & i) = 6 And Not Range("K" & i) = 7 And Not Range("K" & i) = 8 And Not Range("K" & i) = 9 Then
Range("K" & i) = 0
'And when you have done it you want to go down to the next row
Selection.Offset(1, 0).Select
End If
ElseIf .Range("I" & i).Value Like "TRA*" Then
If Not Range("K" & i) = 0 And Not Range("K" & i) = 6 And Not Range("K" & i) = 7 And Not Range("K" & i) = 8 And Not Range("K" & i) = 9 And Not Range("K" & i) = 10 And Not Range("K" & i) = 11 And Not Range("K" & i) = 12 And Not Range("K" & i) = 13 And Not Range("K" & i) = 14 Then
Range("K" & i) = 0
'And when you have done it you want to go down to the next row
Selection.Offset(1, 0).Select
End If
End If
Next i
End With
End Sub
Bookmarks