VB error '1004' Unable to get VLookup property to work right

[NFL]

I'm getting a run-time error 1004 and don't know what is missing. I have 2
columns in the City worksheet as shown below. Thank you!

A B
Location City
1 Springfield
2 Dallas
3 etc. etc..

I want the Master worksheet to look for a city/town in the City worksheet
and place that value in V3.

dim Location as string ' number used to look for city

Worksheets("Master").Activate
With ActiveSheet
.Range("V3") = Application.WorksheetFunction.VLookup(Location,
Sheets("City").Range("A2:B3"), 2, False)

End With

 

[Jacob Skaria]

Try the below...Declared as variant,

Sub Macro()
Dim Location As Variant ' number used to look for city
Location = 1
Worksheets("Master").Activate
With ActiveSheet
..Range("V3") = Application.WorksheetFunction.VLookup(Location, _
Sheets("City").Range("A2:B3"), 2, False)
End With
End Sub

If this post helps click Yes

 

[Dave Peterson]

I'd use:

dim Res as variant 'could be an error

res = application.vlookup(location, sheets("City").range("a2:b3", 2, false)

with Worksheets("Master")
if iserror(res) then
.range("V3").value = "Missing"
else
.range("V3").value = res
end if
end with

==========
Saved from a previous post:

There is a difference in the way application.vlookup() and
worksheetfunction.vlookup() (or application.worksheetfunction.vlookup()) behave.

Application.vlookup returns an error that you can check:

dim Res as variant 'could return an error
res = application.vlookup(....)
if iserror(res) then
msgbox "no match"
else
msgbox res
end if

Application.worksheetfunction.vlookup raises a trappable error that you have to
catch:

dim res as variant
on error resume next
res = application.worksheetfunction.vlookup(...)
if err.number <> 0 then
msgbox "no match"
else
msgbox res
end if
on error goto 0

(application.match() and application.worksheetfunction.match() behave the same
way.)

Personally, I find using the application.vlookup() syntax easier to read. But
it's personal preference.