First time using VLookup

[magelan]

Here is the situation - I have around 50k cells that all have a Country name. Some of them dont have a country name [example is US, GB, DE]. What happens is I am looking at each CITY name and comparing it to a list i've built on the worksheet named Utility in the range a2:B200. in the format


a b
1 PR Vega Baja
2 PR Vieques
3 PR Villalba
etc..

theCity is a range.value being passed to the vlupCity function, and result is a variant that either contains the error code [no match found] or HOPEFULLY the name of the Country [PR]



Sub vlupCity(theCity As String, result As Variant)
result = [VLOOKUP(theCity, Utility!A2:B200, 1, FALSE)]
End Sub

theoretically if theCity contains "Villalba" it should return "PR", and if not found contains an error that i can check with iserror(result). Is my code right?

[Paul_Hossler]

Using Vlookup like that would match Col A and return Col 1, also Col A

Doesn't look 'to the left'

Try just using functions on a WS first to see the technique

Something like this would work


Option Explicit

Sub test()
Dim v As Variant

Call vlupCity2("Boston", v)
If IsEmpty(v) Then
MsgBox "Not found"
Else
MsgBox v
End If
Call vlupCity2("Villalba", v)
If IsEmpty(v) Then
MsgBox "Not found"
Else
MsgBox v
End If
End Sub

Sub vlupCity2(theCity As String, result As Variant)
Dim v As Variant

On Error Resume Next
v = Application.WorksheetFunction.Match(theCity, Worksheets("Utility").Range("$B:$B"), 0)
On Error GoTo 0

If IsEmpty(v) Then
result = v
Else
result = Worksheets("Utility").Cells(v, 1).Value
End If
End Sub


Paul

[magelan]

Using Vlookup like that would match Col A and return Col 1, also Col A

Doesn't look 'to the left'

Try just using functions on a WS first to see the technique

Something like this would work


Option Explicit

Sub test()
Dim v As Variant

Call vlupCity2("Boston", v)
If IsEmpty(v) Then
MsgBox "Not found"
Else
MsgBox v
End If
Call vlupCity2("Villalba", v)
If IsEmpty(v) Then
MsgBox "Not found"
Else
MsgBox v
End If
End Sub

Sub vlupCity2(theCity As String, result As Variant)
Dim v As Variant

On Error Resume Next
v = Application.WorksheetFunction.Match(theCity, Worksheets("Utility").Range("$B:$B"), 0)
On Error GoTo 0

If IsEmpty(v) Then
result = v
Else
result = Worksheets("Utility").Cells(v, 1).Value
End If
End Sub

Paul


Hmm... I tested this out, and here is my final code now.

From the main routine

Call vlupCity(cityRange.Value, resulter)
If Not IsEmpty(resulter) Then 'if result was true, replace.
'[my code here to replace]
End If


and the vlupCity routine

Dim v As Variant
On Error Resume Next
v = Application.WorksheetFunction.Match(theCity, Worksheets("Utility").Range("$B:$B"), 0)
On Error GoTo 0
If IsEmpty(v) Then
result = v
Else
result = Worksheets("Utility").Cells(v, 1).Value
End If


i usually dislike copy-pasting code from online but it is taking me a bit to really understand the process here [like how $B:$B i guess just means the entire column?] and how cells(v,1) somehow gives the offset...

[david000]

i usually dislike copy-pasting code from online but it is taking me a bit to really understand the process here [like how $B:$B i guess just means the entire column?] and how cells(v,1) somehow gives the offset...

Your right that B:B is the entire column. But Cells(v,1) isn't offset. The "v" is the row number that the City was found in, lets say 3 for example and the "1" is the column that you wanted to return the value or the one that has "PR" in it.

Match only returns a row number when it finds a match.

Have you ruled out using a formula for this project and do you need to move all the countries with a PR to a list of undetermined size to the utility sheet?

[Paul_Hossler]

B:B does mean all of col B

Worksheets("Utility").Cells(v, 1).Value means the cell in row v, column 1 of Utility

.Match looks in Col B for the value and returns the row number

.Cells (v,1) uses that row number in column 1 to get the country

Paul

[magelan]

Your right that B:B is the entire column. But Cells(v,1) isn't offset. The "v" is the row number that the City was found in, lets say 3 for example and the "1" is the column that you wanted to return the value or the one that has "PR" in it.

Match only returns a row number when it finds a match.

Have you ruled out using a formula for this project and do you need to move all the countries with a PR to a list of undetermined size to the utility sheet?

My purpose in this is to catch any City name that matches in my list [from PR, GU, VI] and then change the country code in my data to match what it is in the list. Basically cross-reference the list using the city name and correct the country code


If Not IsEmpty(resulter) Then 'if result was true, replace.
replaceCount = replaceCount + 1
countryRange.Value = resulter
End If

[david000]

I'm going with Paul's intuition that you need a "Left" lookup. You may need one a lot more robust than the example I'm leaving here, but I've attached a file as an example. This example only checks the first instance that the city has a match. Vlookup would have done the same thing though.


Sub a()
Dim i As Integer
For i = 1 To Range("E" & Rows.Count).End(xlUp).Row
Cells(i, 4) = Application.Index(Columns(1), Application.Match(Cells(i, 5), Columns(2), 0), 1)
If IsError(Cells(i, 4)) Then
Cells(i, 4) = "Not found"
End If
Next i
End Sub

[Paul_Hossler]

You could reverse the order of

a b
1 PR Vega Baja
2 PR Vieques
3 PR Villalba

into

a b
1 Vega Baja PR
2 Vieques PR
3 Villalba PR

Then the sub could just use


Sub vlupCity2(theCity As String, result As Variant)
Dim v As Variant

On Error Resume Next
v = Application.WorksheetFunction.VLookup(theCity, Worksheets("Utility").Range("$A:$B"),2,False)
On Error Goto 0

result = V

End Sub