Prev: Block a excel file from being copied?
Next: Multi-select from a dropdown list
From: Glenda on 7 Apr 2010 08:25
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))
From: Bob Phillips on 7 Apr 2010 09:01
Try

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


--

HTH

Bob

"Glenda" <Glenda(a)discussions.microsoft.com> wrote in message
news:7AE98B81-239B-41FD-8632-27D92C8143A7(a)microsoft.com...
>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))


 | 
Pages: 1
Prev: Block a excel file from being copied?
Next: Multi-select from a dropdown list