CODEWITHSUNDEEP
excelvba
Runeaway3
Runeaway3

Reputation: 1449

How to set VLookUp with Cells format in VBA?

I want to set the FomrulaR1C1 of a cell to equal a vlookup with a variable table array. I have tried to pass the range into the look up with the Cells format. I am not sure where I am going wrong.

For Each myCell In vLookUpRange

            myCell.FormulaR1C1 = "=VLOOKUP(R[0]C3, 'Parametrics'!Cells(4,3).Address,Cells(4,4).Address,2,0)"

    Next

Can I not use that Cells notation in FormulaR1C1?

Upvotes: 0

Views: 58

Answers (1)

Scott Craner
Scott Craner

Reputation: 152505

No, you cannot use vba notation inside the formula string. It must be removed from the string and concatenated in using &

"=VLOOKUP(R[0]C3,'Parametrics'!" & Cells(4,3).Address(1,1,xlR1C1) & ":" & Cells(4,4).Address(1,1,xlR1C1) & ",2,0)"

the added (1,1,xlR1C1) makes the address absolute and R1C1 style.

You also do not need to loop, just assign the whole at once:

vLookUpRange.FormulaR1C1 = "=VLOOKUP(R[0]C3,'Parametrics'!" & Cells(4,3).Address(1,1,xlR1C1) & ":" & Cells(4,4).Address(1,1,xlR1C1) & ",2,0)"

Upvotes: 3

Related Questions