Macro to find text in list and change value in adjacent cell

[Daniel F]

Hi!

I would like help to make a macro which will find specific suppliers, specified in advance (preferably listed in the macro itself) for example supplier "Alpha" and "Bravo", in column M of a list and for each row where the supplier is found, change the value in column O of that row to the text "Res". The list will have a different amount of rows each month.

The file where i will use this macro will have the same columns each month but will change name so i would have to use the macro from my personal.xlsb

Preferably i would post what code i have so far but its been so long since i last made a macro that i have not really gotten anywhere with it

I appreciate any help you can give

Please ask away if there is any more information i can supply you with. I have read the rules and searched the forum for applicable answers but im new to the forum so please advice if i have missed anything.

Best regards, Daniel

 

[Fluff]

Hi & welcome to the board.
How about

Sub AddRes()

   Dim Ary As Variant
   
   Ary = Array("[COLOR=#ff0000]Hertsmere[/COLOR]", "[COLOR=#ff0000]Stevenage[/COLOR]", "[COLOR=#ff0000]Meriden[/COLOR]")
   With ActiveSheet
      If .AutoFilterMode Then .AutoFilterMode = False
      .Range("M:M").AutoFilter 1, Ary, xlFilterValues
      .Range("O2", .Range("M" & .Rows.Count).End(xlUp).Offset(, 2)).SpecialCells(xlVisible).Value = "Res"
      .AutoFilterMode = False
   End With
End Sub

The sheet with the data will need to be the active sheet when you run it.
Change the values in red to match your suppliers

 

[Daniel F]

Hi there Fluff,

Thank you for the welcome and the code. Works like a charm!

 

[Fluff]

Glad to help & thanks for the feedback