Re: using VLookUp in a VBA program

Tech-Archive recommends: Repair Windows Errors & Optimize Windows Performance

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 worksheet 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 worksheet 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 WorkSheet As String
> > > Dim LookUpValue As String
> > > Dim TableArray As String
> > > Dim ColumnIndexNumber As String
> > >
> > > WorkSheet = "'Links (S)'"
> > > LookUpValue = CStr(ShiftNumber)
> > > TableArray = WorkSheet & "!" & "$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 WORKSHEET 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/
>

Relevant Pages

  • Re: using VLookUp in a VBA program
    ... need to reference anything in order to use it. ... Second, all of the worksheet ... Dim rngLookup As Range ... >>> Dim WorkSheet As String ...
    (microsoft.public.excel.programming)
  • Re: Date change macro between worksheets
    ... since the default name of a new workbook would be Book1 without ... Dim BackupFileName As String ... with the value in a cell of a worksheet from another ... > need to reference the workbooks through the workbooks collection... ...
    (microsoft.public.excel.programming)
  • Re: VBA to rename sheet WITHIN Excel
    ... Thanks Tom, building it up as a string and referencing the string hadn't ... > not be automatically changed if the sheet name or workbook name were to be ... >> This is so that formulae within an excel sheet can reference to the named ...
    (microsoft.public.excel.programming)
  • Re: referencing worksheet as a variable
    ... > reference the new worksheet that is created with that last name as the ... > Dim f As String ...
    (microsoft.public.excel.programming)
  • referencing worksheet as a variable
    ... reference the new worksheet that is created with that last name as the ... Dim f As String ...
    (microsoft.public.excel.programming)