Hi, I am using the code below to find matching city names in two different sheets, A and B. Sheet A col A has over 100 000 city names, and a corresponding code in col B. In sheet B col A I enter multiple city names, run the code that find the corresponding city in sheet A and copy the code to sheet B col B.
The problem is that, especially in the USA, many cities have names that are identical in other countries. That causes the macro to show the wrong code because the wrong "instance" of the city is chosen. Is there a way to rewrite the code I am using to it is possible to deselect one or more countries from a dropdown list, so those countries are not being searched? The country code is always the two first letters in sheet A col B. So if searching for New York but not in the USA (yes, it is existing), then US should be deselected, and then USA will not be searched?
The problem is that, especially in the USA, many cities have names that are identical in other countries. That causes the macro to show the wrong code because the wrong "instance" of the city is chosen. Is there a way to rewrite the code I am using to it is possible to deselect one or more countries from a dropdown list, so those countries are not being searched? The country code is always the two first letters in sheet A col B. So if searching for New York but not in the USA (yes, it is existing), then US should be deselected, and then USA will not be searched?
VBA Code:
Sub Find_Similar()
Dim d As Object, v As Variant, i As Long
Set d = CreateObject("Scripting.Dictionary")
v = Sheets("A").Cells(1, 1).CurrentRegion.Resize(, 2)
For i = LBound(v) To UBound(v)
d(v(i, 2)) = v(i, 1)
Next i
v = Sheets("B").Cells(1, 1).CurrentRegion.Resize(, 2)
For i = LBound(v) To UBound(v)
If d.exists(v(i, 1)) Then v(i, 2) = d(v(i, 1))
Next i
Sheets("B").Cells(1, 1).CurrentRegion.Resize(, 2) = v
End Sub