View Full Version : First time using VLookup
magelan
12-26-2012, 09:22 AM
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
12-26-2012, 09:59 AM
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
12-26-2012, 10:41 AM
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
12-26-2012, 12:03 PM
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
12-26-2012, 12:07 PM
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
12-26-2012, 01:43 PM
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
12-27-2012, 10:48 AM
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
12-27-2012, 04:11 PM
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
Powered by vBulletin® Version 4.2.5 Copyright © 2024 vBulletin Solutions Inc. All rights reserved.