Results 1 to 4 of 4

Thread: Custom VBA VLookup Formula
Thanks Thanks: 0 Likes Likes: 0

  1. #1
    New Member
    Join Date
    Aug 2003
    Location
    NYC
    Posts
    37
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Custom VBA VLookup Formula

    I'm having a hard time getting a custom vlookup formula to work.

    I need to be able to change a part of the range in the vlookup dynamically based on a cell value.

    This is what I came up with but it gives me a value error. Can anyone help?

    Function VL(val As Variant, RNG As Range, col As Integer, BL As Boolean)
    Dim Lookup As Range
    Set Lookup = Range("'C:\AirlineData\Total O&D\[" & RNG & " Total O&D.xls]by market'!$B$1:$D$65536")
    VL = WorksheetFunction.VLookup(val, Lookup, col, BL)
    End Function

  2. #2
    New Member
    Join Date
    Aug 2003
    Location
    NYC
    Posts
    37
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Custom VBA VLookup Formula

    This should work but it's not. I'm using excel 2007 and also my breakpoints dont seem to want to work in my ide

    Function VL(val As Variant, RNG As String, col As Integer)
    MsgBox RNG
    Dim stringCombo As String
    stringCombo = "'C:\AirlineData\Total O&D\[" & RNG & " Total O&D.xls]by market'!$B$1:$D$65536"
    MsgBox stringCombo
    VL = Application.WorksheetFunction.VLookup(val, stringCombo, col, 0)
    End Function


    All the msg boxs come up with the right data

  3. #3
    MrExcel MVP
    Moderator
    RoryA's Avatar
    Join Date
    May 2008
    Location
    UK
    Posts
    33,815
    Post Thanks / Like
    Mentioned
    48 Post(s)
    Tagged
    6 Thread(s)

    Default Re: Custom VBA VLookup Formula

    That won't work unless the other workbook is open and you remove the path and refer to a range object. You can't pass a string to the second argument of WorksheetFunction.Vlookup - it has to be a range object.

  4. #4
    New Member
    Join Date
    Aug 2003
    Location
    NYC
    Posts
    37
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Custom VBA VLookup Formula

    Is there a way I can modify that cell's formula and refresh it with the new vlookup formula?

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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