Moved your needed data over to the right of the data you have and then used this macro.
Not the most concise but easy to follow and modify, hope it is of some assistance.
Code:
Sub imptype()
'to fill in IMPTYP from CLM number
Dim CLMrng As Range 'the range of cells to work with
Dim cel As Range 'the individual cell in CLMrng
Dim LastRow As Long 'the last row used in the CLMrng
Dim c As String 'the value in the cell being worked with
'to avoid slow down from screen updating
Application.ScreenUpdating = False
LastRow = Sheets("Sheet1").Cells(Rows.Count, "A").End(xlUp).Row
Set CLMrng = Sheets("Sheet1").Range("A3:A" & LastRow)
For Each cel In CLMrng
c = Trim(cel.Value)
If c = "111" Then
cel.Offset(0, 3).Value = "Implant Pass-through: Auto Invoice Pricing (AIP)"
ElseIf c = "222" Then
cel.Offset(0, 3).Value = "Implant Pass-through: Auto Invoice Pricing (AIP)"
ElseIf c = "333" Then
cel.Offset(0, 3).Value = "Implant Pass-through: Auto Invoice Pricing (AIP)"
ElseIf c = "444" Then
cel.Offset(0, 3).Value = "Implant Pass-through: PPR Tied to Invoice"
ElseIf c = "555" Then
cel.Offset(0, 3).Value = "Implant Pass-through: PPR Tied to Invoice"
ElseIf c = "666" Then
cel.Offset(0, 3).Value = "Implant Pass-through: PPR Tied to Invoice"
ElseIf c = "777" Then
cel.Offset(0, 3).Value = "Implant Pass-through: Auto Invoice Pricing (AIP)"
End If
Next cel
Application.ScreenUpdating = True
End Sub
Bookmarks