VBA Macro To Fill Missing Items In A Sequence Of Values Based On Criteria

[Admin]

Hi

Another option

Sub imptype()

    Dim c   As Range
    Dim a   As String
    
    Set c = Range("J1:J" & Range("J" & Rows.Count).End(xlUp).Row)
    a = c.Address
    
    c = Evaluate("=if(" & a & "=""aip"",""Implant Pass-through: Auto Invoice Pricing (AIP)"",""Implant Pass-through: PPR Tied to Invoice"")")

End Sub

[Rick Rothstein]

Here are two more options... the first one is mostly to show yet another alternative, but I seriously doubt you will find a faster method than the second one.

' Interesting alternative
'
Sub ImpType1()
  With Range("J1:J" & Cells(Rows.Count, "J").End(xlUp).Row)
    .Replace "AIP", "=AIP", xlWhole
    .SpecialCells(xlConstants).Clear
    .SpecialCells(xlBlanks).Value = "Implant Pass-through: PPR Tied to Invoice"
    .SpecialCells(xlFormulas).Value = "Implant Pass-through: Auto Invoice Pricing (AIP)"
  End With
End Sub

' Super fast method
'
Sub ImpType2()
  Dim X As Long, vArr As Variant
  vArr = Range("J1:J" & Cells(Rows.Count, "J").End(xlUp).Row)
  For X = 1 To UBound(vArr)
    If vArr(X, 1) = "AIP" Then
      vArr(X, 1) = "Implant Pass-through: Auto Invoice Pricing (AIP)"
    Else
      vArr(X, 1) = "Implant Pass-through: PPR Tied to Invoice"
    End If
  Next
  Range("J1:J" & Cells(Rows.Count, "J").End(xlUp).Row) = vArr
End Sub