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

N

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
 
J

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
 
D

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.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Cant find VLookup property 5
Vlookup Error 3
Error 1004 3
VLookup in VBA Help Needed 9
Run Time Error '1004' 1
Error 1004 17
No error message but code doesn't work 1
Run Time Error 1004 in Loop 0

Top