Apr 27 2021 05:44 PM
Hopefully this is an easy one for someone. I am just trying to run an Array of vlookup on a spreadsheet. I can't get the vlookup Lookup value to be dynamic. It just copies the same cell call-out to every formula in the array.
This is a fairly simple formula. It runs and copies the vlookup formula to all of the cells in the row, but I can figure out how to make the Lookup value dynamic like it would if i were just in the sprreadsheet and copied the formula down the column.
I've tried several other setups for the A6 value, but it just copies that text over.
Ultimately I just want the formula to run and then to just be left with the value. I can't seem to get that to work, but haven't gotten over the first hurdle of course.
Any help would be great. I've searched many websites.
Code:
---------------------------------------------------------------
Sheets("Master AttributeDB").Activate
Application.ScreenUpdating = False
Application.Calculation = xlCalculationAutomatic
lr = Cells.Find("*", Cells(1, 1), xlFormulas, xlPart, xlByRows, xlPrevious, False).Row
Range("U6:U" & lr).FormulaArray = "=vlookup(a6,ReferenceToolAttributes,1,0)"
---------------------------------------------------------------
I even tried a different approach:
Range("U6:U" & lr).FormulaArray.VLookup((Range("A6:A" & lr)), Range("ReferenceToolAttributes"), 1, 0)
Apr 27 2021 09:51 PM
Try this instead:
Range("U6:U" & lr).FormulaR1C1 = "=VLOOKUP(RC[-20],ReferenceToolAttributes,1,0)"
Apr 30 2021 05:19 PM
@JMB17 That worked perfectly. Thank you so much. Saved me a lot of time and the macro literally runs 100x faster than the way I was doing it prior. Much appreciated
Apr 30 2021 08:25 PM