Find and Replace based upon an array or similar

[Imrhien]

G'day folks!

The people here have been a massive help with getting my macro working. I'm down to the last part of it now.

The situation: I regularly upload address databases to my shipping system. Unfortunately, the shipping system is quite precise about what suburb/town combinations I can use. One example is:
Town: Auckland
Suburb: Green bay
My software expects:
Town: Waitakere
Suburb: Green Bay
Because of a recent change in the addressing in Auckland, most address databases I upload will have the former address, which won't validate.

So what I want to do is get excel to search through the SUBURB column and replace the neighbouring TOWN cell with an appropriate value. I have a spreadsheet right now with all the combinations the shipping software will accept. In this case it's acceptable to assume that the SUBURB data is correct. If there are SUBURBs that exist in two TOWNS, I have deleted those references from the Combinations spreadsheet so that it's impossible to accidentally assign the wrong TOWN. Spelling mistakes and duplicate suburbs can be fixed manually when uploading the data, so that's not a priority. This is basically a brute attempt to purify the data as much as possible and reduce manual work.

So I'm guessing that I need to somehow put the TOWN and SUBURB data into a big 2d array, and then create a FIND/REPLACE loop that will roll through the suburb column, find matches in the SUBURB array and replace the neighbouring TOWN cell with the suburb's associated town value.

Here's my current code for reference. Yes, it's awful

Columns("K:K").Select
    Do
        'North Shore Fixes
        If UCase(ActiveCell.Value) = "ALBANY" Then
        ActiveCell.Offset(0, -1).Value = "North Shore City"
        End If
        If UCase(ActiveCell.Value) = "TAKAPUNA" Then
        ActiveCell.Offset(0, -1).Value = "North Shore City"
        End If
        If UCase(ActiveCell.Value) = "MAIRANGI BAY" Then
        ActiveCell.Offset(0, -1).Value = "North Shore City"
        End If
    
        ActiveCell.Offset(1, 0).Select

    Loop Until IsEmpty(ActiveCell.Offset(0, 2))

Can anyone offer any advice? I know I'm asking a lot here.

 

[Imrhien]

nudge

 

[Imrhien]

I just need to find a way to start...I can't figure out how to import my data into the array for starters!