I need a specific range as my vlookup range on a sheet called "Merchant List". I don't want the R1C1 reference that excel uses when creating the r1c1 formula. My code is below. When I try it I am getting an error. I want the reference cell to be from Column 3, but the lookup table to be "C3:H1000" on the "Merchant List" sheet. Please advise what is wrong with my code and how I can fix it. Thanks:


Dim Rng99 as range

Sheets("Merchant List").select
range("C3:H1000").Select
Set Rng99 = Selection

Sheets("Transact Revenue").Select
range(endmerchant).Select


ActiveCell.Offset(0, -2).Select
ActiveCell.FormulaR1C1 = _
"=VLOOKUP(RC3,'Merchant List'!" & Rng99.Address & ",3,False)"

Thank you,

J