Macro/VBA for detecting a string and then putting a word in a new column

[carab]

I know this can be done with a simple formula, but I have a lot of other macros going on with this worksheet and thousands of lines of data that changes every day so I would prefer a macro.

Here is what I would like to happen in every single row:
If Cell B2 contains "023001" or "023002", then put "Supplier" in column Q.


Column B contains an assortment of characters, so it wouldn't JUST be "023001" - there could be letters/numbers on either side of that particular set of numbers.
Column Q is the next empty column in the worksheet. This is where I want the word "Supplier" to appear if column B has "023001" or "023002" in it.

Thanks in advance!

 

[HalfAce]

Hi carab, welcome to the board.
Depending on how many thousands of rows you're talking about, this may take a bit to execute but it should work and will certainly be quicker than doing it by hand.

Sub MarkSuppliersInColumnQ()
Dim LstRw As Long, Rw As Long
LstRw = Cells(Rows.Count, "B").End(xlUp).Row
Application.ScreenUpdating = False
For Rw = 2 To LstRw
    If InStr(Cells(Rw, "B").Value, "023001") Or InStr(Cells(Rw, "B").Value, "023002") Then _
        Cells(Rw, "Q").Value = "Supplier"
Next Rw
Application.ScreenUpdating = True
End Sub

Hope it helps.

 

[carab]

HalfAce - it works beautifully, thank you so much!

Hi carab, welcome to the board.
Depending on how many thousands of rows you're talking about, this may take a bit to execute but it should work and will certainly be quicker than doing it by hand.

Sub MarkSuppliersInColumnQ()
Dim LstRw As Long, Rw As Long
LstRw = Cells(Rows.Count, "B").End(xlUp).Row
Application.ScreenUpdating = False
For Rw = 2 To LstRw
    If InStr(Cells(Rw, "B").Value, "023001") Or InStr(Cells(Rw, "B").Value, "023002") Then _
        Cells(Rw, "Q").Value = "Supplier"
Next Rw
Application.ScreenUpdating = True
End Sub

Hope it helps.

 

[HalfAce]

You're most welcome. Glad it helped.