VLookup Variable Offset
 

I want to use a macro to set an active cell to return a value from the 2nd
column of an array called RollLastRecost. If specific row is empty I want no
value to be the result. I have a formula that works if I manually type it
in, and copy it down through the other rows but I want the macro to put the
formula in for each row using ActiveCell.FormulaR1C1. I'm not sure how to
make the B3 cell reference change as the macro moves through the other rows.
This is what I use manually

If(ISNA(VLookup(B3, RollLastRecost,2,False)),"
",VLookUp_(B3,RollLastRecost,2,False))

VLookup Variable Offset
 

Try

Range("A3").Resize(8).FormulaR1C1 = _
"=IF(ISNA(VLOOKUP(R[]C2,RollLastRecost,2,False)),"""",VLOOKUP(R[3]C2,RollLastRecost,2,False))"


--

HTH

Bob

"Glenda" wrote in message
...
I want to use a macro to set an active cell to return a value from the 2nd
column of an array called RollLastRecost. If specific row is empty I want
no
value to be the result. I have a formula that works if I manually type it
in, and copy it down through the other rows but I want the macro to put
the
formula in for each row using ActiveCell.FormulaR1C1. I'm not sure how to
make the B3 cell reference change as the macro moves through the other
rows.
This is what I use manually

If(ISNA(VLookup(B3, RollLastRecost,2,False)),"
",VLookUp_(B3,RollLastRecost,2,False))