Find value in separate range and replace

[SAE01]

Sheet1, column A contains the following data:

BOFL
BOF Locations
Building our Future Locations

These are all variations of the same same value, just written in slightly different ways.

In Sheet 2, Column A, I have listed all of the variations (I may need to add more variations in future). I want the macro to run though each cell in Sheet1, ColumnA. If the cell value matches one of the variations in the list on Sheet 2, Column A, replace the Sheet 1 value with "BOFL".

This comes with the added complication that the list in sheet 1 then goes onto another set of variations:

CFtF
Compliance for the future
Compliance FtF

The standard return for these should be "CFTF", and the list of variations are held in Sheet 2, Column B. So i'd like to code to move on to standardising these.

My second point is that I'm not sure if holding a list of variations on sheet 2 is the most efficient way to do this. Can the variations been written into the code, and new ones simply added to the code as they're uncovered?

 

[Fluff]

How about

Sub StandardiseData()

   Dim Ary1 As Variant
   Dim Ary2 As Variant
   Dim Cl As Range
   
   Ary1 = Array("BOF Locations", "Building our Future Locations")
   Ary2 = Array("Compliance for the future", "Compliance FtF")
   
   For Each Cl In Range("A2", Range("A" & Rows.Count).End(xlUp))
      If UBound(Filter(Ary1, Cl.Value, True, vbTextCompare)) >= 0 Then
         Cl.Value = "BOFL"
      ElseIf UBound(Filter(Ary2, Cl.Value, True, vbTextCompare)) >= 0 Then
         Cl.Value = "CFTF"
      End If
   Next Cl
   
End Sub

 

[SAE01]

This is excellent - works perfectly. Thank you so much!

 

[Fluff]

Glad to help & thanks for the feedback