Re: using VLookUp in a VBA program

From: nano_electronix (nano_electronix.11jv1i_at_excelforum-nospam.com)
Date: 02/13/04 

Date: Thu, 12 Feb 2004 23:30:56 -0600

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/