Results 1 to 7 of 7
  1. #1
    New Lounger
    Join Date
    Mar 2002
    Location
    Jacksonville, Florida, USA
    Posts
    10
    Thanks
    0
    Thanked 0 Times in 0 Posts

    vlookup in VBA (MSO97)

    I have a table called "Kit_Table" in a spreadsheet filenamed "get001.xls"

    In Excel spreadsheet, the formula =Vlookup(B32, 'get001.xls'!Kit_Table,2,False) works just fine.

    In my macro, I would like to make the colum index number a variable. I tried
    Kit_Part = WorksheetFunction.VLookup(kit_no, Range("get001.xls!Kit_Table"), i, False)
    with no luck. error = Method 'Range' of object'_Global' failed

    Any hints?

    Thanks!

    Mark <img src=/S/brickwall.gif border=0 alt=brickwall width=25 height=15>

  2. #2
    Silver Lounger
    Join Date
    Mar 2001
    Location
    Springfield, Ohio, USA
    Posts
    2,136
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: vlookup in VBA (MSO97)

    Mark, I think that the error message is really saying that VBA cannot resolve links to unopened workbooks. If you open the workbook first, then the vlookup should work. I didn't try it, so if you have problems post back. HTH --Sam
    <font face="Comic Sans MS">Sam Barrett, CACI </font face=comic>
    <small>And the things that you have heard... commit these to faithful men who will be able to teach others also. 2 Timothy 2:2</small>

  3. #3
    New Lounger
    Join Date
    Mar 2002
    Location
    Jacksonville, Florida, USA
    Posts
    10
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: vlookup in VBA (MSO97)

    The macro is executed from a spreadsheet that contains the table, so it is an openned workbook.

  4. #4
    Silver Lounger
    Join Date
    Mar 2001
    Location
    Springfield, Ohio, USA
    Posts
    2,136
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: vlookup in VBA (MSO97)

    Mark, if the lookupValue is in the named range(cell) Kit_Num and the tableArray is in the named range Kit_Table, then
    <pre> MsgBox Application.WorksheetFunction.VLookup( _
    Range("Kit_Num"), Range("Kit_Table"), 2, False)</pre>

    will do the vLookup. See attached workbook. HTH --Sam
    Attached Files Attached Files
    <font face="Comic Sans MS">Sam Barrett, CACI </font face=comic>
    <small>And the things that you have heard... commit these to faithful men who will be able to teach others also. 2 Timothy 2:2</small>

  5. #5
    Silver Lounger
    Join Date
    Dec 2000
    Location
    California, USA
    Posts
    1,758
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: vlookup in VBA (MSO97)

    Sam,

    What happens in the instance where you are unable to get the VLookup property of the worksheet function class. As an example the file you attached works as long as the VLookup returns True. If you try looking up 4 it errors out. Is there some type of method to handle the errors such as Msgbox "Not Found"?

    John

  6. #6
    Silver Lounger
    Join Date
    Dec 2000
    Location
    California, USA
    Posts
    1,758
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: vlookup in VBA (MSO97)

    Try:

    Sub Look()
    On Error GoTo ErrorHandler
    MsgBox Application.WorksheetFunction.VLookup( _
    Range("Kit_Num"), Range("Kit_Table"), 2, False)
    Exit Sub
    ErrorHandler:
    MsgBox "Not Found In List."
    End Sub

  7. #7
    Platinum Lounger
    Join Date
    Feb 2001
    Location
    Weert, Limburg, Netherlands
    Posts
    4,812
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: vlookup in VBA (MSO97)

    Excel doesn't understand the range statement in the middle of the vlookup:

    Kit_Part = WorksheetFunction.VLookup(kit_no, Range("get001.xls!Kit_Table"), i, False)

    Instead do this:

    Kit_Part = WorksheetFunction.VLookup(kit_no, workbooks("get001.xls").worksheets("WHateverSheet" ).Range("Kit_Table"), i, False)
    Jan Karel Pieterse
    Microsoft Excel MVP, WMVP
    www.jkp-ads.com
    Professional Office Developers Association

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •