load vlookup results from another (closed) workbook into an open workbook using vba

[twinwings]

Hello,

So I am pretty sure this issue has come up before, but I can't seem to solve it using any of the past answers regarding similar situations.

What I would *like* Excel to do is.

vlookup data in cell A3, from a currently open workbook ---- and match it against data in a closed workbook in the following path, and return me the results as values.


The path for the workbook that has the data is this:
S:\a\b\c\d\e\f\g\warrantyL12.xlsx

Then, I would like excel to paste the result on my currently open workbook as values. So vlookup cell a3, from my current open workbook against data in warrantyL12.

Here's my code so far

Sub lookuptest()

Dim wb1 As Workbook
Dim wb2 As Workbook
Dim Range1, myValue
Set wb1 = ThisWorkbook
Set wb2 = Workbooks.Open("S:\a\b\c\d\e\f\g\warrantyL12.xlsx")


Range1 = wb2.Sheets("data").Range("A:K")
ActiveCell = Application.WorksheetFunction.VLookup(A3, Range1, 11, False)


End Sub

On this code, I end up opening the file and the macro gets stuck at "ActiveCell".
I guess I never specified where I would like the results to be pasted, although as far as I can understand, ActiveCell is the cell I have last selected (and the cell I have last selected is where I would like my data to be pasted).


Eventually, I will end up having a worksheet that has about 100 unique cells in column A, and columns B to Z have data that are the results of vlookups of cells from Column A, but obviously each column has a different kind data.

Currently, all I do is select B3 to Z3, and double click on the corner upon which the formulas are effectively pasted down to the last row.

But sometimes I have several thousand rows, and it takes a long time for all the columns with all the vlookups to fill through. Furthermore, I just need the data as values so vlookups also take up space just by being active, untilI paste over as values.

In the past, I have seen macro enabled spreadsheets that with a click of a button, a bunch of data is filled out. The data is filled out in the same logic as a vlookup would fill it out based on data from the leftest column, but they are all pasted as values.


I understand this post is pretty convoluted, but I hope you guys get the idea.

Thank you for your help,
tw

 

[twinwings]

The code you posted initially and that I modified in Post #6 does a lookup over the range A:K and returns column 11, that's column K. Now you say you want to return column I, that's 9 not 11 so you need to modify the code accordingly.

Hi Joe,

I guess that would help, but I can always change the range.

I think the main issue I face is basically having Excel match the cell apple, in the master file, and return to me whatever corresponding cell in the column that I specify based on the vlookup.



On my workbook that only has fruits, I will probably have around 8 columns.

If you can show me how to do it for one of the columns, (so in the scenario above, have column B filled out with data from column I), then I am pretty sure I can do it for the remaining columns in the fruit only workbook as well.

 

[JoeMo]

Hi Joe,

I guess that would help, but I can always change the range.

I think the main issue I face is basically having Excel match the cell apple, in the master file, and return to me whatever corresponding cell in the column that I specify based on the vlookup.



On my workbook that only has fruits, I will probably have around 8 columns.

If you can show me how to do it for one of the columns, (so in the scenario above, have column B filled out with data from column I), then I am pretty sure I can do it for the remaining columns in the fruit only workbook as well.

If you want to use a fruit in A3 of the workbook you run the macro from (wbA) as the lookup value to be found in a closed workbook (wbB) whose full file path is "S:\a\b\c\d\e\f\g\warrantyL12.xlsx" in the used range from col A to col K on a sheet named "data", and return the value from column I in that workbook (wbB) to B3 in wbA then this should work:

Sub lookuptest()
Dim wbA As Workbook
Dim sFilename As String
Dim Range1 As Range
Set wbA = ThisWorkbook
sFilename = "S:\a\b\c\d\e\f\g\warrantyL12.xlsx" 'Change to suit the path to workbook B
With GetObject(sFilename)
    Set Range1 = Intersect(.Sheets("data").Range("A:K"), .Sheets("data").UsedRange)
    Range("B3").Value = Application.WorksheetFunction.VLookup(Range("A3").Value, Range1, 9, False)
    .Close
End With
End Sub

 

[Aristide]

If you want to use a fruit in A3 of the workbook you run the macro from (wbA) as the lookup value to be found in a closed workbook (wbB) whose full file path is "S:\a\b\c\d\e\f\g\warrantyL12.xlsx" in the used range from col A to col K on a sheet named "data", and return the value from column I in that workbook (wbB) to B3 in wbA then this should work:

Sub lookuptest()
Dim wbA As Workbook
Dim sFilename As String
Dim Range1 As Range
Set wbA = ThisWorkbook
sFilename = "S:\a\b\c\d\e\f\g\warrantyL12.xlsx" 'Change to suit the path to workbook B
With GetObject(sFilename)
    Set Range1 = Intersect(.Sheets("data").Range("A:K"), .Sheets("data").UsedRange)
    Range("B3").Value = Application.WorksheetFunction.VLookup(Range("A3").Value, Range1, 9, False)
    .Close
End With
End Sub

I'm very aware of the fact that this is an old thread, but in the above I believe there's a mistake in referencing the wrong cell. To my opinion, the OP had the "apple" info for the wbA (the open workbook) in cell A1 and not in A3.

Best,
--Aristide

 

[Aristide]

Sub lookuptest()
Dim wbA As Workbook
Dim sFilename As String
Dim Range1 As Range
Set wbA = ThisWorkbook
sFilename = "S:\a\b\c\d\e\f\g\warrantyL12.xlsx" 'Change to suit the path to workbook B
With GetObject(sFilename)
    Set Range1 = Intersect(.Sheets("data").Range("A:K"), .Sheets("data").UsedRange)
    Range("B3").Value = Application.WorksheetFunction.VLookup(Range("A3").Value, Range1, 9, False)
    .Close
End With
End Sub

Creating the Workbook variable wbA seems redundant because of not used...

 

[emn47]

If you want to use a fruit in A3 of the workbook you run the macro from (wbA) as the lookup value to be found in a closed workbook (wbB) whose full file path is "S:\a\b\c\d\e\f\g\warrantyL12.xlsx" in the used range from col A to col K on a sheet named "data", and return the value from column I in that workbook (wbB) to B3 in wbA then this should work:

Sub lookuptest()
Dim wbA As Workbook
Dim sFilename As String
Dim Range1 As Range
Set wbA = ThisWorkbook
sFilename = "S:\a\b\c\d\e\f\g\warrantyL12.xlsx" 'Change to suit the path to workbook B
With GetObject(sFilename)
    Set Range1 = Intersect(.Sheets("data").Range("A:K"), .Sheets("data").UsedRange)
    Range("B3").Value = Application.WorksheetFunction.VLookup(Range("A3").Value, Range1, 9, False)
    .Close
End With
End Sub

Hello,
The code is nice but how do I add it for column B:B and not for b3.
Range("B3").Value = Application.WorksheetFunction.VLookup(Range("A3").Value, Range1, 3, False)
Range("B4").Value = Application.WorksheetFunction.VLookup(Range("A4") .Value, Range1, 4, False)
Range("B5").Value = Application.WorksheetFunction.VLookup(Range("A5").Value, Range1, 5, False) . . . . like...

Let the bidet run automatically when data is entered in cell A