-2
For Each c In LookupRange

    Cells(c.Row, 15).Activate
    Selectedcell = ActiveCell

    If InStr(Selectedcell, "PLATE") > 0 Then
        Cells(c.Row, 18).FormulaR1C1 = "PP07"
    End If

    If InStr(Selectedcell, "PIPE") > 0 Then
        Cells(c.Row, 18).FormulaR1C1 = "PP10"
    End If

    If InStr(Selectedcell, "NUT") > 0 Then
        Cells(c.Row, 18).FormulaR1C1 = "PP02"
    End If

    If InStr(Selectedcell, "STUD") > 0 Then
        Cells(c.Row, 18).FormulaR1C1 = "PP02"
    End If

    If InStr(Selectedcell, "BOLT") > 0 Then
        Cells(c.Row, 18).FormulaR1C1 = "PP02"
    End If

    'ELSE IF
    'Cells(c.Row, 18).FormulaR1C1 = "PP07"

Next c


Cells(9, 2).Activate
0m3r
  • 12,286
  • 15
  • 35
  • 71
Dakota W Maxwell
  • 31
  • 5
  • 3
    This [SO question and answers](https://stackoverflow.com/questions/10714251/how-to-avoid-using-select-in-excel-vba) will help get you started. However, if this is functional code, it's better at [CodeReview.se] which is designed for these types of questions as opposed to here at [SO] which is designed for fixing broken code. – FreeMan Nov 01 '18 at 18:09
  • Seems like it should `If "PLATE" Else If "PIPE" Else If "NUT" Or "STUD" Or "BOLT"` – Marcucciboy2 Nov 01 '18 at 18:12

1 Answers1

1

For example:

Sub Tester()

    Dim c As Range, txt, res, LookupRange As Range

    Set LookupRange = Range("B7:B16") 'or whatever

    For Each c In LookupRange.Cells

        txt = c.Value
        res = ""
        Select Case True
            Case txt Like "*NUT*", txt Like "*STUD*", txt Like "*BOLT*"
                res = "PP02"
            Case txt Like "*PLATE*"
                res = "PP07"
            Case txt Like "*PIPE*"
                res = "PP10"
            Case Else
                res = "PP07"
        End Select

        c.EntireRow.Cells(18).Value = res
    Next c

End Sub

Though it's not clear from your posted code if the various cases are all mutually exclusive.

Tim Williams
  • 154,628
  • 8
  • 97
  • 125
  • This case is for individual rows. For example, it is checking to see if row 1 = any of the strings mentioned, then setting a separate cell = to a PP#. – Dakota W Maxwell Nov 01 '18 at 19:28
  • This is a straight refactoring of your posted code: if it doesn't do what you need then you will need to edit your question explain exactly what you want to achieve. – Tim Williams Nov 27 '18 at 00:14