Sub vlookup()

Dim myrange As Range
dim Fred as variant
Set myrange = Workbooks("first.xls").Worksheets("sheet1").Range( "list")

fred = Application.vlookup(Range("a1"), myrange, 2, True)

if iserror(fred) then
range("B1") = "missing"
else
range("B1") = fred
end if

End Sub

I dropped the .worksheetfunction from your code. Application.vlookup() returns
an error that you can check for.

Application.worksheetfunction.vlookup() causes a run time error (if no match)
that you have to code around.

on error resume next
fred =Application.WorksheetFunction.vlookup(Range("a1") , myrange, 2, True)
if err.number < 0 then
fred = "missing"
err.clear
end if

range("B1").value = fred

===
I find the application.vlookup() easier.

pjjclark wrote:

Below is what I've writtem so far:

Sub vlookup()

Dim myrange As Range

Set myrange =
Workbooks("first.xls").Worksheets("sheet1").Range( "list")

fred = Application.WorksheetFunction.vlookup(Range("a1"), myrange, 2,
True)

Range("b1") = fred

End Sub

The contents of "first.xls" has numbers 1 to 10 in column A and letters
a to i in column B.

My issue is if I change the 'true' value to 'false' within the vlookup
function. It works fine until I enter a value that is not in my list, I
get a 'error 1004 unable to get the vlookup property of the
worksheetfunction class' message box come up. I am expecting a '#N/A'
instead.

I'm using excel 2003 with vb 6.3.

Any ideas?

--
pjjclark
------------------------------------------------------------------------
pjjclark's Profile: http://www.excelforum.com/member.php...o&userid=29180
View this thread: http://www.excelforum.com/showthread...hreadid=508959

--

Dave Peterson