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
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
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
Code:
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 SubOn 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
