Results 1 to 6 of 6

Thread: Vlookup VBA

  1. #1
    New Lounger
    Join Date
    Sep 2013
    Posts
    17
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Vlookup VBA

    Hello,

    Some Background:

    worksheet "sheet1" with a 2-column range called "Name" (Name + Email)
    worksheet "sheet2" cell a1 with the requested Name
    To get Email address on worksheet "sheet2" cell b1.

    I want to use Vlookup in a vba module, but i can't figure it out how to do this...

  2. #2
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts
    In an excel formula you could do it simply in B1 with:
    =VLOOKUP(Sheet2!A1,Name,2,0)

    Or with VBA:
    Worksheets("sheet2").Range("B1") = Application.WorksheetFunction.VLookup _
    (Worksheets("sheet2").Range("A1"), Range(ThisWorkbook.Names("name")), 2, 0)

    Steve

  3. #3
    New Lounger
    Join Date
    Sep 2013
    Posts
    17
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Quote Originally Posted by sdckapr View Post
    Or with VBA:
    Worksheets("sheet2").Range("B1") = Application.WorksheetFunction.VLookup _
    (Worksheets("sheet2").Range("A1"), Range(ThisWorkbook.Names("name")), 2, 0)

    Steve
    VBA gives Run-time error '1004' Application-defined....

  4. #4
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts
    what is the complete code you are using?
    Is your worksheet set up like you described?

    Could you attach an example file?
    Steve

  5. #5
    New Lounger
    Join Date
    Sep 2013
    Posts
    17
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Quote Originally Posted by sdckapr View Post

    Could you attach an example file?
    Steve
    Hi Steve, I have attached the example file with details.
    Attached Files Attached Files

  6. #6
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts
    As I suspected the sheet is not setup like you described [there is no range called "Name"; your lookup is 3 columns not 2; you are looking up on the code to get teh name and email, not looking up on the name to get the email, etc].

    I am not sure what you need the VLOOKUP macro to do exactly. You can populate the table with formulas which will be "live" if the items in Sheet1 changes...

    In Sheet2!A2 (expand the range as desired):
    =VLOOKUP($B2,Sheet1!$A$2:$C$7,3,0)

    In Sheet2!C2 (expand the range as desired):
    =VLOOKUP($B2,Sheet1!$A$2:$C$7,2,0)

    You can copy A2:C2 down the columns as desired to fill in the lookup table...

    Steve

Posting Permissions

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