vlookup, true false issue

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?

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

Using worksheetfunction as a qualifier causes it to raise a trappable error
rather than return #N/A, so trap the error.

Sub vlookup()
Dim myrange As Range
On Error goto ErrHandler
Set myrange =Workbooks("first.xls").Worksheets("sheet1").Range("list")
fred = Application.WorksheetFunction.vlookup( _
Range("a1"), myrange, 2,False)
Range("b1") = fred
Exit Sub
ErrHandler:
msgbox Range("A1") & " was not found

End Sub

--
Regards,
Tom Ogilvy


"pjjclark" <pjjclark.22tfam_1139242208.5991@excelforum-nospam.com> wrote in
message news:pjjclark.22tfam_1139242208.5991@excelforum-nospam.com...
>
> 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
>

To get THAT message , I think you are on a sheet where a1 is blank

try this
Sub vlookup_Don()
Set myrange = Workbooks("20060201.xls"). _
Worksheets("mysheet").Range("list")
Range("b1") = Application.vlookup([a1], myrange, 2, 0)

End Sub
--
Don Guillett
SalesAid Software
dguillett1@austin.rr.com
"pjjclark" <pjjclark.22tfam_1139242208.5991@excelforum-nospam.com> wrote in
message news:pjjclark.22tfam_1139242208.5991@excelforum-nospam.com...
>
> 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
>

Thanks for your help.

It works, at last I can now stop banging my head!!

And after reading Don's message, I noticed that he changed the sub's name.

I missed that. I don't think it's a good idea to use a name of a worksheet
function as your sub's name.

Dave Peterson wrote:
>
> 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

--

Dave Peterson

Just for information if you are actually trying to learn something:
I your look at Don's code, the reason it works is because he used
application.Vlookup rather than Application.worksheetFunction.Vlookup. If
A1 is blank that would be problematic as well, but he did nothing in his
code to correct for that - only made it slower by replacing Range("A1") with
[a1]

--
Regards,
Tom Ogilvy

"pjjclark" <pjjclark.22thdn_1139244906.0195@excelforum-nospam.com> wrote in
message news:pjjclark.22thdn_1139244906.0195@excelforum-nospam.com...
>
> Thanks for your help.
>
> It works, at last I can now stop banging my head!!
>
>
> --
> pjjclark
> ------------------------------------------------------------------------
> pjjclark's Profile:
http://www.excelforum.com/member.php...o&userid=29180
> View this thread: http://www.excelforum.com/showthread...hreadid=508959
>

I just re-tested. If a1 is blank you still get n/a. Isn't this what OP asked
for?
I only used [a1] for brevity in the formula part and didn't notice an
appreciable change in speed. Perhaps this is more important with very slow
computers with little memory. <G>


--
Don Guillett
SalesAid Software
dguillett1@austin.rr.com
"Tom Ogilvy" <twogilvy@msn.com> wrote in message
news:Og33u7zKGHA.648@TK2MSFTNGP14.phx.gbl...
> Just for information if you are actually trying to learn something:
> I your look at Don's code, the reason it works is because he used
> application.Vlookup rather than Application.worksheetFunction.Vlookup. If
> A1 is blank that would be problematic as well, but he did nothing in his
> code to correct for that - only made it slower by replacing Range("A1")
> with
> [a1]
>
> --
> Regards,
> Tom Ogilvy
>
> "pjjclark" <pjjclark.22thdn_1139244906.0195@excelforum-nospam.com> wrote
> in
> message news:pjjclark.22thdn_1139244906.0195@excelforum-nospam.com...
>>
>> Thanks for your help.
>>
>> It works, at last I can now stop banging my head!!
>>
>>
>> --
>> pjjclark
>> ------------------------------------------------------------------------
>> pjjclark's Profile:
> http://www.excelforum.com/member.php...o&userid=29180
>> View this thread:
>> http://www.excelforum.com/showthread...hreadid=508959
>>
>
>

and if A1 isn't blank and you put in a non matching value it puts in n/a as
well, so being blank or not is not part of the issue and had no bearing on
the original problem. Sorry you had to exert extra time retesting.

Using [a1] instead of Range("A1") had no bearing on solving the problem as
well.

You like to use it, so use it.

If you disagree that using application.Vlookup vice
WorksheetFunction.Vlookup elimintates the 1004 error on a non match please
enlighten us.

--
Regards,
Tom Ogilvy



"Don Guillett" <dguillett1@austin.rr.com> wrote in message
news:Oni4LW1KGHA.1192@TK2MSFTNGP11.phx.gbl...
> I just re-tested. If a1 is blank you still get n/a. Isn't this what OP
asked
> for?
> I only used [a1] for brevity in the formula part and didn't notice an
> appreciable change in speed. Perhaps this is more important with very slow
> computers with little memory. <G>
>
>
> --
> Don Guillett
> SalesAid Software
> dguillett1@austin.rr.com
> "Tom Ogilvy" <twogilvy@msn.com> wrote in message
> news:Og33u7zKGHA.648@TK2MSFTNGP14.phx.gbl...
> > Just for information if you are actually trying to learn something:
> > I your look at Don's code, the reason it works is because he used
> > application.Vlookup rather than Application.worksheetFunction.Vlookup.
If
> > A1 is blank that would be problematic as well, but he did nothing in his
> > code to correct for that - only made it slower by replacing Range("A1")
> > with
> > [a1]
> >
> > --
> > Regards,
> > Tom Ogilvy
> >
> > "pjjclark" <pjjclark.22thdn_1139244906.0195@excelforum-nospam.com> wrote
> > in
> > message news:pjjclark.22thdn_1139244906.0195@excelforum-nospam.com...
> >>
> >> Thanks for your help.
> >>
> >> It works, at last I can now stop banging my head!!
> >>
> >>
> >> --
> >> pjjclark
>
>> ------------------------------------------------------------------------
> >> pjjclark's Profile:
> > http://www.excelforum.com/member.php...o&userid=29180
> >> View this thread:
> >> http://www.excelforum.com/showthread...hreadid=508959
> >>
> >
> >
>
>