Help with FormulaArray and Vlookup

%3CLINGO-SUB%20id%3D%22lingo-sub-2300143%22%20slang%3D%22en-US%22%3EHelp%20with%20FormulaArray%20and%20Vlookup%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2300143%22%20slang%3D%22en-US%22%3E%3CP%3EHopefully%20this%20is%20an%20easy%20one%20for%20someone.%26nbsp%3B%20I%20am%20just%20trying%20to%20run%20an%20Array%20of%20vlookup%20on%20a%20spreadsheet.%26nbsp%3B%20I%20can't%20get%20the%20vlookup%20Lookup%20value%20to%20be%20dynamic.%26nbsp%3B%20It%20just%20copies%20the%20same%20cell%20call-out%20to%20every%20formula%20in%20the%20array.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThis%20is%20a%20fairly%20simple%20formula.%26nbsp%3B%20It%20runs%20and%20copies%20the%20vlookup%20formula%20to%20all%20of%20the%20cells%20in%20the%20row%2C%20but%20I%20can%20figure%20out%20how%20to%20make%20the%20Lookup%20value%20dynamic%20like%20it%20would%20if%20i%20were%20just%20in%20the%20sprreadsheet%20and%20copied%20the%20formula%20down%20the%20column.%3C%2FP%3E%3CP%3EI've%20tried%20several%20other%20setups%20for%20the%20A6%20value%2C%20but%20it%20just%20copies%20that%20text%20over.%26nbsp%3B%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EUltimately%20I%20just%20want%20the%20formula%20to%20run%20and%20then%20to%20just%20be%20left%20with%20the%20value.%26nbsp%3B%20I%20can't%20seem%20to%20get%20that%20to%20work%2C%20but%20haven't%20gotten%20over%20the%20first%20hurdle%20of%20course.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EAny%20help%20would%20be%20great.%26nbsp%3B%20I've%20searched%20many%20websites.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ECode%3A%3C%2FP%3E%3CP%3E---------------------------------------------------------------%3C%2FP%3E%3CP%3ESheets(%22Master%20AttributeDB%22).Activate%3CBR%20%2F%3EApplication.ScreenUpdating%20%3D%20False%3CBR%20%2F%3EApplication.Calculation%20%3D%20xlCalculationAutomatic%3CBR%20%2F%3E%3CBR%20%2F%3Elr%20%3D%20Cells.Find(%22*%22%2C%20Cells(1%2C%201)%2C%20xlFormulas%2C%20xlPart%2C%20xlByRows%2C%20xlPrevious%2C%20False).Row%3C%2FP%3E%3CP%3E%3CBR%20%2F%3ERange(%22U6%3AU%22%20%26amp%3B%20lr).FormulaArray%20%3D%20%22%3Dvlookup(a6%2CReferenceToolAttributes%2C1%2C0)%22%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E---------------------------------------------------------------%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20even%20tried%20a%20different%20approach%3A%3C%2FP%3E%3CP%3ERange(%22U6%3AU%22%20%26amp%3B%20lr).FormulaArray.VLookup((Range(%22A6%3AA%22%20%26amp%3B%20lr))%2C%20Range(%22ReferenceToolAttributes%22)%2C%201%2C%200)%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-2300143%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2300541%22%20slang%3D%22en-US%22%3ERe%3A%20Help%20with%20FormulaArray%20and%20Vlookup%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2300541%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1038959%22%20target%3D%22_blank%22%3E%40ahavelind%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ETry%20this%20instead%3A%3C%2FP%3E%3CP%3E%3CSPAN%3ERange(%22U6%3AU%22%20%26amp%3B%20lr)%3C%2FSPAN%3E.FormulaR1C1%20%3D%20%22%3DVLOOKUP(RC%5B-20%5D%2CReferenceToolAttributes%2C1%2C0)%22%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2312892%22%20slang%3D%22en-US%22%3ERe%3A%20Help%20with%20FormulaArray%20and%20Vlookup%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2312892%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F675152%22%20target%3D%22_blank%22%3E%40JMB17%3C%2FA%3E%26nbsp%3B%26nbsp%3BThat%20worked%20perfectly.%20Thank%20you%20so%20much.%20Saved%20me%20a%20lot%20of%20time%20and%20the%20macro%20literally%20runs%20100x%20faster%20than%20the%20way%20I%20was%20doing%20it%20prior.%20Much%20appreciated%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2313041%22%20slang%3D%22en-US%22%3ERe%3A%20Help%20with%20FormulaArray%20and%20Vlookup%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2313041%22%20slang%3D%22en-US%22%3EThat's%20good%20to%20hear%20-%20you're%20quite%20welcome.%3C%2FLINGO-BODY%3E
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.