Thanks Thanks:  0
Likes Likes:  0
Results 1 to 3 of 3

Thread: Another VLookup question.

  1. #1
    Board Regular
    Join Date
    Mar 2002
    Location
    Cincinnati, Ohio, USA
    Posts
    6,824
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Hi all.
    I am using the VLookup function in VBA and can not get it to work. Please help...

    Ex.




    | A | B |
    |______________|_________________|
    1 | 1300 | How |
    |______________|_________________|
    2 | 1518 | Are |
    |______________|_________________|
    3 | 1614 | You |
    |______________|_________________|
    4 | 2001 | Doing |
    |______________|_________________|
    5 | 2124 | Today |
    |______________|_________________|




    The range A1:B5 is named:
    E_List

    How do I incorporate this named range into
    my VLookup function?

    Here is my current code. I keep getting an error.




    CurrentDept = "1518"

    Set E_AddressRange = ActiveWorkbook.Names("E_List")

    E_Address = Application.WorksheetFunction.VLookup(Val(CurrentDept), E_AddressRange, 2)





    E_Address should return "Are"

    Another question:
    Can I also get this function to return the row number?
    In this example RowNum would equal 2.

    Thanks,
    Tom

  2. #2
    Board Regular
    Join Date
    Feb 2002
    Location
    Calgary, Alberta Canada
    Posts
    3,496
    Post Thanks / Like
    Mentioned
    2 Post(s)
    Tagged
    1 Thread(s)

    Default



    The following UDF (User Defined Function) works. I used named ranges Code and E_List.

    Use Function as =LookupA(CODE, E_List)

    Function LookupA(CODE, E_List)
    If CODE <> "" Then
    With Application.WorksheetFunction
    LookupA = .VLookup(CODE, E_List, 2, False)
    End With
    Else
    LookupA = ""
    End If
    End Function

  3. #3
    Board Regular
    Join Date
    Mar 2002
    Location
    Cincinnati, Ohio, USA
    Posts
    6,824
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Thanks Dave,
    Works perfectly...
    I was about to give up on this one and just code the formula to a worksheet and use it from there...
    Thanks Again!
    Tom

Some videos you may like

User Tag List

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
  •