Re: using VLookUp in a VBA program

From: Rob van Gelder (newsgroups_at_nojunkmail-vangelder.co.nz)
Date: 02/13/04 

Date: Fri, 13 Feb 2004 18:57:32 +1300

It's a Typo. I'm sure Tom meant:
WorksheetFunction.VLookup 

-- 
Rob van Gelder - http://www.vangelder.co.nz/excel
"nano_electronix >" <<nano_electronix.11jv1i@excelforum-nospam.com> wrote in
message news:nano_electronix.11jv1i@excelforum-nospam.com...
> First of all, thank you all for your reply. Please read on.
>
>
>
> Yes VLookup works fine as a work*** formula, but i was thinking of
> saving some coding by reusing the existing excel functions.
>
>
>
>
> I've tried the following as suggested by Tom
>
> Dim TempString As String
> TempString = Application.VLookup("201", Me.Range("A1:F20"), 4,
> True)
> MsgBox (TempString)
>
> Application.VLookup is not detected by intellisense (so it's probably
> not the correct reference to the Vlookup function) and when I executed
> the statement it returns with "Type Mismatch Error".
>
>
>
>
> I have also tried the -> Tools -> Add-in and selected all the packages
> as suggested by Barry.
>
> It returned the same error. "Unabled to get the Vlookup property of the
> worksheetfunction class.  I would have thought that I am suppose to
> include the reference from VBE so that my program can reference the
> Analysis Tool, but there are so many packages i am not sure which to
> include for the work*** function.
>
> I have tried including the following 2 packages in the reference
> 1. atpvbaen.xls
> 2. funcres
> But it still returns with the same error.
>
>
>
>
> James
>
>
>
>
> Tom Ogilvy wrote:
> > *Try application.Vlookup(value, range reference, column number, true
> > or
> > false)
> >
> > no, arguments are not passed as strings (see above).
> >
> > --
> > Regards,
> > Tom Ogilvy
> >
> > "nano_electronix >" <<nano_electronix.11hyc9@excelforum-nospam.com>
> > wrote in
> > message news:nano_electronix.11hyc9@excelforum-nospam.com...
> > > Hi all,
> > >
> > > I am trying to use VLookUp in VBA. My code is as follows
> > >
> > > Dim Work*** As String
> > > Dim LookUpValue As String
> > > Dim TableArray As String
> > > Dim ColumnIndexNumber As String
> > >
> > > Work*** = "'Links (S)'"
> > > LookUpValue = CStr(ShiftNumber)
> > > TableArray = Work*** & "!" & "$E$1:$G$70"
> > > ColumnIndexNumber = "1"
> > >
> > > ShiftType = Application.WorksheetFunction.VLookup(LookUpValue,
> > > TableArray, ColumnIndexNumber)
> > >
> > > When I run it, it says that it can't find VLookUp as an
> > > WorksheetFunction. I am not sure how I can make it work.
> > >
> > > I have also tried Excel.WorksheetFunction.VLookup without success.
> > >
> > > Another sub-question is, when i use EXCEL WORK*** FUNCTIONS, I
> > assume
> > > that all arguements to the FUNCTIONS are supplied as STRINGS. Is
> > this a
> > > correct assumption?
> > >
> > >
> > >
> > > James
> > >
> > >
> > > ---
> > > Message posted from http://www.ExcelForum.com/
> > > *
>
>
> ---
> Message posted from http://www.ExcelForum.com/
>