VBA ActiveCell.Formula issue

**Bedlam** · 11-07-2011, 10:44 AM

hello happy monday to every one lol... I'm needing a bit of help when it comes to pasting this formula in cell B2 down. Just for simplicity for now NumRows = 100 but i cannot get this formula to work in my worksheet. It needs to change from A2 to A3 etc but I am not too great on using FormulaR1C1. If anyone could give me help it would be fantastic. Thanks everyone!

    Cells(1, 2) = "Deleted Account Names"
    For Row = 1 To NumRows
    Cells(Row + 1, 2).Select
    ActiveCell.Formula = "=IF(LEFT(A2,4)='RSOF',VLOOKUP(A7,'Summary Total'!A12:B131,2,FALSE),VLOOKUP(A2,Composites!$A$12:$B$132,2,FALSE))"
    Columns.AutoFit
    Next Row

**Domski** · 11-07-2011, 10:51 AM

Try:

Cells(1, 2) = "Deleted Account Names"
Cells(2, 2).Resize(NumRows, 1).Formula = "=IF(LEFT(A2,4)=""RSOF"",VLOOKUP(A7,'Summary Total'!A12:B131,2,FALSE),VLOOKUP(A2,Composites!$A$12:$B$132,2,FALSE))"
Columns.AutoFit

Dom