Help with FormulaArray and Vlookup

Occasional Contributor

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)

3 Replies

@ahavelind 

 

Try this instead:

Range("U6:U" & lr).FormulaR1C1 = "=VLOOKUP(RC[-20],ReferenceToolAttributes,1,0)"

@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

That's good to hear - you're quite welcome.