Hello,
I have some VBA code I was using where I was looping through a range to add vlookups to a list.
I'm trying to do the same thing for a similar project but have a way larger dataset. 50k lines so it's pretty inefficient to use a loop.
I'm trying to add the formula to the first item on the list and then copy and paste the formula to the range. I did something similar to a concatenation to create the vlookup id's and it worked.
The issue I'm having is the vlookup is copying the formula but it is copying the vlookup for the first item all the way down. I need to formula to act dynamically when I copy and paste the formulas. Any idea on how to solve this?
Here is my code:
I have some VBA code I was using where I was looping through a range to add vlookups to a list.
I'm trying to do the same thing for a similar project but have a way larger dataset. 50k lines so it's pretty inefficient to use a loop.
I'm trying to add the formula to the first item on the list and then copy and paste the formula to the range. I did something similar to a concatenation to create the vlookup id's and it worked.
The issue I'm having is the vlookup is copying the formula but it is copying the vlookup for the first item all the way down. I need to formula to act dynamically when I copy and paste the formulas. Any idea on how to solve this?
Here is my code:
VBA Code:
'ADD VLOOKUP AND AUTOFILL QUOTA
j = Worksheets("Upload").Range("A:A").Cells.SpecialCells(xlCellTypeConstants).Count
c = Worksheets("Upload").Range("D:D").Cells.SpecialCells(xlCellTypeConstants).Count + 1
Cells(c, 4).Select
ActiveCell = Application.WorksheetFunction.VLookup(Sheets("Upload").Range("K" & c), Sheets("Quota").Range("A:O"), Sheets("Upload").Range("J2"), False)
Cells(c, 4).Copy
Worksheets("Upload").Range("D" & 3 & ":D" & j).PasteSpecial Paste:=xlPasteFormulas
Worksheets("Upload").Range("D" & 2 & ":D" & j).Copy
Worksheets("Upload").Range("D" & 2 & ":D" & j).PasteSpecial Paste:=xlPasteValues