"Autofill" Array Formula RRS feed

  • Question

  • I have vba code which will insert array formula into cell C2, however i need this code to go down the whole of column C from C2 downwards (C2:C).

    With ThisWorkbook.Sheets("Tester").Range("E2:E2")
            .FormulaArray = "=IF(ISERROR(Z),IF(ISERROR(Y),IF(ISERROR(AA),VLOOKUP(INDEX('Resource Table'!H$4:H$20,MATCH(1,(- -ISNUMBER(AB)),0),0),'Resource Table'!H$4:I$20,2,),AA),Y),Z)"
            .Replace "AA", "VLOOKUP(Tester!G2,Data!G:J,4,)"
            .Replace "AB", "SEARCH('Resource Table'!H$4:H$20,Tester!F9)"
            .Replace "Z", "VLOOKUP(D2,Data!F:J,5,)"
            .Replace "Y", "VLOOKUP(F2,Data!H:J,3,)"
    End With

    the problem is that i need certain cell references in the formula to change.

    i.e when formula in cell C3, D2 changes to D3, F2 to F3, G2 to G3 and so on for every C

    Thursday, June 11, 2015 11:43 PM