Results 1 to 3 of 3
  1. #1
    3 Star Lounger
    Join Date
    Feb 2001
    Posts
    369
    Thanks
    2
    Thanked 1 Time in 1 Post

    VLOOKUP in VBA problem with ' (2000sp3)

    I'm trying to use VLOOKUP in VBA in order to have a lookup function in personal.xls which I can use from anywhere and change the source appropriately, but the vb compiler is baulking at the ' preceding the file path. So Application.WorksheetFunction.Vlookup(parameter, RefSheet!A:B,2,False) works fine, but Application.WorksheetFunction.Vlookup(parameter, 'Z:my documents[reference workbook.xls]RefSheet'!A:B,2,False) does not. What do I need to do to make this work?

    Thanks

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: VLOOKUP in VBA problem with ' (2000sp3)

    I don't see how using RefSheet!A:B could work in VBA, it is not a valid reference to a range. [RefSheet!A:B] should be OK, as well as

    Application.WorksheetFunction.VLookup(parameter, ['Z:my documents[reference workbook.xls]RefSheet'!A:B], 2, False)

    but only if the reference workbook is open, so you might as well use

    Application.WorksheetFunction.VLookup(parameter, ['[reference workbook.xls]RefSheet'!A:B], 2, False)

    The more common way of doing this would be to use

    Application.WorksheetFunction.VLookup(parameter, Workbooks("reference workbook.xls").Worksheets("RefSheet").Range("A:B") , 2, False)

  3. #3
    3 Star Lounger
    Join Date
    Feb 2001
    Posts
    369
    Thanks
    2
    Thanked 1 Time in 1 Post

    Re: VLOOKUP in VBA problem with ' (2000sp3)

    Thanks Hans,

    I have to admit I'm always confused about the way worksheet functions refer to ranges in other workbooks - I always end up using the function wizard if I have to, and copying the syntax from the resultant formula - hence the syntax which you think won't work.

    I'm glad you pointed out the "usual" technique, as I didn't think I could mix VBA and "workbook" range selection methods like that. Now I know you can that's the best way to do it, as I'm at home with that syntax.

    Cheers

Posting Permissions

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