change city based on zip code

[sparky101747]

I have a worksheet names, addresses, etc. what iI would like to do is have a macro if there is are certain zip codes in column D, I would like to change the city name in Column C.

Like if in column D = "95110" clear contents of corresponding cell in Coulumn C = " Greater Downtown-Metro Area"

I have a list of many zip codes/cities that need to be changed.

Thank You in Advance

 

[MrKowz]

What is your list of Zip codes to change, and what is your list of Cities to change them to?

 

[sparky101747]

Here You go:

95110=Greater Downtown-Metro Area
95111=South San Jose
95112=Greater Downtown-Metro Area
95113=Greater Downtown-Metro Area
95116=Berryessa
95117=West San Jose
95118=Cambrian
95119=Santa Teresa
95120=Almaden
95121=Evergreen
95122=Evergreen
95123=Blossom Valley
95124=Cambrian
95125=Willow Glen
95126=West San Jose
95127=East San Jose
95128=West San Jose
95129=West San Jose
95130=West San Jose
95131=North San Jose
95132=Berryessa
95133=Berryessa
95134=North San Jose
95135=Evergreen
95136=Blossom Valley
95138=Evergreen
95139=Santa Teresa
95140=East San Jose
95141=Almaden
95148=Evergreen

Thanks

 

[MrKowz]

Give this a shot on a copy of your data:

Public Sub ChangeCity()
Dim ZipList     As Variant, _
    CityList    As Variant, _
    i           As Long, _
    LR          As Long, _
    x           As Variant
 
ZipList = Array(95110, 95111, 95112, 95113, 95116, 95117, 95118, 95119, 95120, _
                95121, 95122, 95123, 95124, 95125, 95126, 95127, 95128, 95129, _
                95130, 95131, 95132, 95133, 95134, 95135, 95136, 95138, 95139, _
                95140, 95141, 95148)
 
CityList = Array("Greater Downtown-Metro Area", "South San Jose", "Greater Downtown-Metro Area", _
                 "Greater Downtown-Metro Area", "Berryessa", "West San Jose", "Cambrian", _
                 "Santa Teresa", "Almaden", "Evergreen", "Evergreen", "Blossom Valley", "Cambrian", _
                 "Willow Glen", "West San Jose", "East San Jose", "West San Jose", "West San Jose", _
                 "West San Jose", "North San Jose", "Berryessa", "Berryessa", "North San Jose", _
                 "Evergreen", "Blossom Valley", "Evergreen", "Santa Teresa", "East San Jose", _
                 "Almaden", "Evergreen")
 
LR = Range("D" & Rows.Count).End(xlUp).row
Application.ScreenUpdating = False
 
For i = 1 To LR
    x = Application.Lookup(Range("D" & i).Value, ZipList)
    If Not IsError(x) Then
        Range("C" & i).Value = CityList(x)
    End If
Next i
 
Application.ScreenUpdating = True
End Sub

 

[sparky101747]

I get subscript out of range here

Range("C" & i).Value = CityList(x)

 

[MrKowz]

Try this - I adjusted a couple of lines.

Public Sub ChangeCity()
Dim ZipList     As Variant, _
    CityList    As Variant, _
    i           As Long, _
    LR          As Long, _
    x           As Variant
ZipList = Array(95110, 95111, 95112, 95113, 95116, 95117, 95118, 95119, 95120, _
                95121, 95122, 95123, 95124, 95125, 95126, 95127, 95128, 95129, _
                95130, 95131, 95132, 95133, 95134, 95135, 95136, 95138, 95139, _
                95140, 95141, 95148)
CityList = Array("Greater Downtown-Metro Area", "South San Jose", "Greater Downtown-Metro Area", _
                 "Greater Downtown-Metro Area", "Berryessa", "West San Jose", "Cambrian", _
                 "Santa Teresa", "Almaden", "Evergreen", "Evergreen", "Blossom Valley", "Cambrian", _
                 "Willow Glen", "West San Jose", "East San Jose", "West San Jose", "West San Jose", _
                 "West San Jose", "North San Jose", "Berryessa", "Berryessa", "North San Jose", _
                 "Evergreen", "Blossom Valley", "Evergreen", "Santa Teresa", "East San Jose", _
                 "Almaden", "Evergreen")
LR = Range("D" & Rows.Count).End(xlUp).row
Application.ScreenUpdating = False
For i = 1 To LR
    x = Application.Match(Range("D" & i).Value, ZipList, 0)
    If Not IsError(x) Then
        Range("C" & i).Value = CityList(x - 1)
    End If
Next i
Application.ScreenUpdating = True
End Sub

 

[sparky101747]

Looks like that took care of it, thank you!!