VBA troubles with Vlookup

[Sam]

I am trying to use the vlookup function in a VBA event that I read in John
Walkenbach's "Excel 2000 programming for Dummies". Even though I am using
Excel97, I don't see why it won't work. I have a stupid little 2 column 5
row pricelist called "Store". I wrote this code:
Sub GetPrice()
myItem = InputBox("Enter the item number")
myPrice = Application.WorksheetFunction._
VLookup(myItem, Range("Store"), 2)
MsgBox "The price you requested is " & myPrice
End Sub

I get the error "Unable to get the vlookup property of the WorksheetFunction
Class"

What am I doing wrong. Other functions such as Max work. Why won't Vlookup?

Sam

[Myrna Larson]

You may not be doing anything wrong. I sometimes get that error, too,
particularly with the MATCH function. IMO, it's a bug. Try omitting the
WorksheetFunction piece, i.e.

myPrice = Application.VLookup(myItem, Range("Store"), 2)

[Sam]

Thanks for responding, Myrna, but that gives a "Type Mismatch" error.

"Myrna Larson" <myrna...@home.net> wrote in message
news:gmn1hsgqe4cadcna8...@4ax.com...

[Sam]

Thank you for offering help, Denny. Were you able to solve the problem with
the Val function? It did not work for me. I still get the error "Unable to
get the Vlookup property of the WorksheetFunction Class".

I then tried another table using a letter and two digits (e.g. R17) and got
the same error--that is using InputBox without Val.

This sure would be useful if I could get it to work! Any other ideas?

Sam
"Denny Campbell" <cam...@hotmail.com> wrote in message
news:uMavv4Xt$GA.278@cppssbbsa04...
> Sam,
>
> I was able to duplicate both of your error messages when the "item number"
> was a number rather than a string.
>
> Changing your first line to:
>
> myItem = Val(InputBox("Enter the item number"))
>
> should take care of your problem. It won't work if "item number" is a
> string (D100FV).
>
>
> --
> Denny Campbell
> Grand Rapids, Michigan
>
>
>
> "Sam" <hend...@hotmail.com> wrote in message
> news:#MkatPWt$GA.253@cppssbbsa04...

> > I am trying to use the vlookup function in a VBA event that I read in
John
> > Walkenbach's "Excel 2000 programming for Dummies". Even though I am
using
> > Excel97, I don't see why it won't work. I have a stupid little 2 column
5
> > row pricelist called "Store". I wrote this code:
> > Sub GetPrice()
> > myItem = InputBox("Enter the item number")
> > myPrice = Application.WorksheetFunction._
> > VLookup(myItem, Range("Store"), 2)
> > MsgBox "The price you requested is " & myPrice
> > End Sub
> >
> > I get the error "Unable to get the vlookup property of the
> WorksheetFunction
> > Class"
> >
> > What am I doing wrong. Other functions such as Max work. Why won't
> Vlookup?
> >

> > Sam
> >
> >
>
>

[Denny Campbell]

[Denny Campbell]

It did work for me. I've sent you a copy of my workbook.

--
Denny Campbell
Grand Rapids, Michigan

"Sam" <hend...@hotmail.com> wrote in message

news:ecTwhXYt$GA....@cppssbbsa02.microsoft.com...

> Thank you for offering help, Denny. Were you able to solve the problem
with
> the Val function? It did not work for me. I still get the error "Unable to
> get the Vlookup property of the WorksheetFunction Class".
>
> I then tried another table using a letter and two digits (e.g. R17) and
got
> the same error--that is using InputBox without Val.
>
> This sure would be useful if I could get it to work! Any other ideas?
>
> Sam
> "Denny Campbell" <cam...@hotmail.com> wrote in message
> news:uMavv4Xt$GA.278@cppssbbsa04...