VLookup in different workbook

  • I have problem with Vlookup in different workbook.
    I took code from below link as a baseline, but somehow I cannot make it work.


    https://powerspreadsheets.com/excel-vba-vlookup/


    Any ideas of what I did wrong?


    I have 2 files.
    File 1:

    • worksheet "PRJ"
    • value from column "Z" needs to be looked up in File 2

    File 2:

    • worksheet "Page 1"
    • the same values are in column "A" and
    • value that is searched for is in column "F"

    Value after being looked up needs to be paste in File 1, column "AA" in corresponding row


    Formula that would work here if I hand't try macro is:

    Code
    =VLOOKUP(Z7;'[File 2xlsx]Page 1'!$A:$F;6;FALSE)


    Macro I have so far:


  • Should the myLookupValue line be?

    Code
    myLookupValue = wb1.Range("Z7").Value

    Where there is a will there are many ways. Finding one that works for you is the challenge!

  • Changed to what you proposed, and unfortunately that haven't changed the outcome. Whole macro seems to have 0 effect on both files.
    I have no idea what I did wrong here :(

  • I'm wondering if you need to even reference wb1.... isn't that the workbook your macro is running from.... maybe your lookup value should just be

    Code
    myLookupValue = Sheets("PRJ").Range("Z7").Value

    Where there is a will there are many ways. Finding one that works for you is the challenge!

  • You are correct I'm running macro from the same workbook as referenced wb1
    I tried the change you wrote, but that haven't work either.

    I appreciate your support here.

  • How is it not working? Are you getting an error? If so, which? Are your writing the result anywhere? I don't see that in your code.

    Where there is a will there are many ways. Finding one that works for you is the challenge!

  • So that might be an issue - so for writing the result what should I put in macro?
    Google search didn't helped as all example macros didn't had that part.


    Apologies for probably silly question.
    I guess it will be something along this line?

    Code
    Selection.Copy
        Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
            :=False, Transpose:=False
  • If you want the result in a specific cell then you need to state that in code:


    e.g.


    after


    Code
    myVLookupResult = WorksheetFunction.VLookup(myLookupValue, myTableArray, myColumnIndex, False)
    Range("B2").Value = myVLookupResult

    Where there is a will there are many ways. Finding one that works for you is the challenge!

  • That got us somewhere! Thank you.
    The result now empty cell (if I click on it it shows: 1900-01-00) while value for vlookup should be Test2
    I double checked that output value is not from File 2

  • Are you able to post the workbooks? it's hard to say what is wrong without seeing them. you can remove/obscure sensitive data.

    Where there is a will there are many ways. Finding one that works for you is the challenge!

  • You declared myVlookupResult incorrectly as Long when it should be as String....


    Also, if you running macro from another sheet within the workbook, you should reference the sheets to ensure the right sheet is manipulated.


    Where there is a will there are many ways. Finding one that works for you is the challenge!

  • It worked! You are a star! Thank you so much!


    Any tips on how to make it go through all the cells? I have for now defined my lookup value as specific cell and output as well.
    Macro for Vlookup is only called if value in column AA is "Team", and that seems to work , but I'm not sure how to make it run on correct row than.

  • If you want to have the formula entered in the full Z column, you can try replacing


    Code
    myVLookupResult = WorksheetFunction.VLookup(myLookupValue, myTableArray, myColumnIndex, False)


    with


    Code
    With Sheets("PRJ").Range("AA7:AA58")
            .Formula = "=VLOOKUP(Z7,'[File 2.xlsx]Page 1'!" & myTableArray.Address & "," & myColumnIndex & ",False)"
            .Value = .Value
        End With

    Where there is a will there are many ways. Finding one that works for you is the challenge!

  • that works for the whole column.


    What if I would like to change just specific cells?
    i.e. in column AA there is either value "Team" or value with a specific name of team member.


    First part of macro calls vlookup each time it founds value "Team"



    How to make Vlookup understand that it now needs to lookup value in cell Z7 and next time it's called it needs to lookup value in cell Z16 for example?
    Cells will change based on "Team" value in column AA


    Do I make something easy very complicated? Maybe there is easier way than this?


    I have just 6 months experience with macros and I use mostly google a lot for solving problems. Maybe I'm overdoing it?

  • I am not really a VBA expert, so there is probably a better/more efficient way.... but you'll want to pass the row number to the VLookup2 function each time so that the lookup/result row will be updated each time you loop....


    try revising the Vlookup and Vlookup2 functions to:


    Where there is a will there are many ways. Finding one that works for you is the challenge!

  • One more issue that I've encountered.
    In Vlookup function if it doesn't found value it pastes empty cells, I would prefer for it to leave value as it was "Team" so next round the vlookup will go through this it will check it again.
    I've read that IfError could be used here, but my attempts didn't change the outcome.


    Any idea on how to solve it?

  • Try Changing this:


    Code
    Sheets("PRJ").Range("AA" & cRow).Value = myVLookupResult


    to this:


    Code
    If myVLookupResult <> "" Then
            Sheets("PRJ").Range("AA" & cRow).Value = myVLookupResult
        End If

    Where there is a will there are many ways. Finding one that works for you is the challenge!

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!