VLOOLUP - MACRO sheet different from source and destination

Question

Good day I have a sheet called "MACRO" that runs several macros using predefined workbooks as source and destination.

In this particular requirement, I want to run a macro in my macro sheet, which will use a vlookup on "book2.xslx" with lookup cell, looks up values in "book3.xlsx" and updates "book2.xlsx" with the results

All the macros out there run the vlookup from the active sheet, however i couldnt find anything to do the above.

Please help :)

Thanks

To be more precise i am trying to do the following:

Open "book2.xlsx", go to column "b" "cell 2" and insert the following formula "=VLOOKUP(A2,[Book3.xlsx]Sheet1!$A:$B,2,0)" The same formula to be used all the way till "A the lookup cell" becomes blank", while the value in "A2" should change everytime it moves to the cell below

Can you please help?

Edited: Code im trying to run:

Sub VLOOKUP_DEPT()

Dim wbk1 As Workbook

strFirstFile = "C:\Users\hayekn\Desktop\book2.xlsx"

Set wbk1 = Workbooks.Open(strFirstFile)

 With wbk1.Sheets("sheet1")

Range("B2") = Application.WorksheetFunction.vlookup(Range("A2"), Workbooks("C:\Users\hayekn\Desktop\book3.xlsx").Sheets("sheet1").Range("A:B"), 2, 0)

End With


wbk1.Close True

MsgBox ("VLOOLUP-DEPT Completed!!")

End Sub

I have also tried this, it runs through but does not return anything in "book2"

Sub VLOOKUP_DEPT()

Dim wbk1 As Workbook

strFirstFile = "C:\Users\hayekn\Desktop\book2.xlsx"
strSecondFile = "C:\Users\hayekn\Desktop\book3.xlsx"

Set wbk1 = Workbooks.Open(strFirstFile)
Set wbk2 = Workbooks.Open(strSecondFile)

Set Rng = wbk1.Sheets("Sheet1").Range("B2")
Set Rng2 = wbk1.Sheets("Sheet1").Range("A2")
Set Rng3 = wbk2.Sheets("sheet1").Range("A:B")

Rng = Application.vlookup(Rng2, Rng3, 2, 0)

wbk1.Close True
wbk2.Close True

MsgBox ("VLOOLUP-DEPT Completed!!")

End Sub

Thanks

[a good place to start](http://stackoverflow.com/q/10714251/445425) — chris neilsen, Jul 03 '14 at 06:14
To be more precise i am trying to do the following: Open "Sheet1.xlsx", go to column "d" "cell 1" and insert the following formula "=VLOOKUP(A2,'C:\Documents and Settings\Khawajan\Desktop\New Folder[book1.xlsx]Sheet1'!$A:$C,2,FALSE)" The same formula to be used all the way to "d100", while the value "A2" should change everytime it moves to the cell below ie("d2 will have a2", "d3 will have a3" as the lookup value cell — Nadz, Jul 03 '14 at 07:02
Does simply copying that formula down the sheet not produce the desired result? — chris neilsen, Jul 03 '14 at 07:04
Hi chris. Off course but im doing this for 12 sheets regularly on daily base to update few reports. Thats why im introducing macro to automate the entire thing. — Nadz, Jul 03 '14 at 07:45
If you want help with debugging code, post the code you have, what it currently does, what you want it to do, any errors you get and the line of code that causes the error. — chris neilsen, Jul 03 '14 at 09:16
Hi Chris. Sure i will as soon as i get back to the office. appreciated — Nadz, Jul 04 '14 at 01:46
This is all very confusing. 1) you declare a with block then don't use it. 2) your `Range("B2")` and `"A2"` refer to cells on the Active sheet. Refer to the first link I gave you to avoid this. 3) the lookup refers to a different wb to the one you opened. Is this what you want? Suggest you declare variable for _all_ your book, sheet and range references, then use those in your lookup code. — chris neilsen, Jul 07 '14 at 07:34
Hi Chris. Thank you for the reply. Basically, i want to point to the cell "b2" as my active cell, and then run the formula "VLOOKUP(A2,[Book3.xlsx]Sheet1!$A:$B,2,0)". The lookup does refer to a closed workbook in this location "C:\Users\hayekn\Desktop\". It's very straight forward but i dont seem to be getting the correct syntax. hope this clarifies the question. thanks — Nadz, Jul 07 '14 at 07:40
the magic piece of info that Book3 is _closed_ ! I'll post an answer shortly — chris neilsen, Jul 07 '14 at 07:50
:) sorry. I posted an updated code (after refering to the link you provded) that opens the closed workbook to urn the macro and then closes it again, but im not getting the actual vlookup value into my cell in "book2". — Nadz, Jul 07 '14 at 07:55

chris neilsen · Accepted Answer · 2014-07-07T08:25:30.577

Unfortunately VBA VLookup doesn't work on a closed workbook. But a cell formula does.

Also worth noting

Declare all variables
Seperating the references from the logic makes maintenance easier

A refactored version of your Sub to work on a Closed workbook

Sub VLOOKUP_DEPT()
    Dim strFirstFile As String
    Dim strLookup As String
    Dim wbk1 As Workbook
    Dim wbkLookup As Workbook
    Dim clLookup As Range
    Dim clDest As Range


    strFirstFile = "C:\Users\hayekn\Desktop\book2.xlsx"
    Set wbk1 = Workbooks.Open(strFirstFile)

    strLookup = "'C:\Users\hayekn\Desktop\[book3.xlsx]Sheet1'!A:B"

    With wbk1.Sheets("sheet1")
        Set clDest = .Range("B2")
        Set clLookup = .Range("A2")
    End With

    clDest.Formula = "=VLOOKUP(" & clLookup.Address & "," & strLookup & ",2,0)"
    clDest.Value = clDest.Value ' Convert to value
    wbk1.Close True

    MsgBox ("VLOOLUP-DEPT Completed!!")
End Sub

Modified version to fill down for all used rows in Lookup column

Sub VLOOKUP_DEPT()
    Dim strFirstFile As String
    Dim strLookup As String
    Dim wbk1 As Workbook
    Dim wbkLookup As Workbook
    Dim clLookup As Range
    Dim clDest As Range
    Dim rws As Long

    strFirstFile = "C:\Users\hayekn\Desktop\book2.xlsx"
    Set wbk1 = Workbooks.Open(strFirstFile)

    strLookup = "'C:\Users\hayekn\Desktop\[book3.xlsx]Sheet1'!A:B"

    With wbk1.Sheets("sheet3")
        Set clDest = .Range("B2")
        Set clLookup = .Range("A2")
    End With

    If clLookup.Offset(1, 0) <> vbNullString Then
        rws = Range(clLookup, clLookup.End(xlDown)).Rows.Count
        Set clDest = clDest.Resize(rws, 1)
    End If

    clDest.Formula = "=VLOOKUP(" & clLookup.Address(False, False) & "," & strLookup & ",2,0)"
    clDest.Value = clDest.Value ' Convert to value
    wbk1.Close True

    MsgBox ("VLOOLUP-DEPT Completed!!")
End Sub

Hi Chris, It works great :-D but keeps prompting me to select the "worksheet" in book3. I think there is a need to insert the worksheet name "sheet1" in Book3 to avoid the prompt. C:\Users\hayekn\Desktop\[book3.xlsx].sheets[sheet1].'!A:B (perhaps?) — Nadz, Jul 07 '14 at 08:09
Awesome :-D I wouldn't have found this solution on my own, did not think it should be declared. Something else came up though, is it difficult to apply this for all the rows below until "A" in "book2" is blank? just like "dragging" the formula downwards — Nadz, Jul 07 '14 at 08:16
Hi Chris, Firstly, thank you for the help, truly appreciate the time taken to get support. I tried to run the new code however it doesn't return a any value in book2. Im trying to figure it out but cant seem to find the issue — Nadz, Jul 07 '14 at 08:59
Figured it out, it was reading ith wbk1.Sheets("sheet3"). Changed to "sheet1", worked :-D. Again thank you so much for your help, saved me so much time as i would apply this to over 15 sheets — Nadz, Jul 07 '14 at 09:01

VLOOLUP - MACRO sheet different from source and destination

1 Answers1