Snasui Excel Forum

Code: Select all

Sub Macro1()
    Range("AC2").Select
    ActiveCell.FormulaR1C1 = "=IF(RC[-28]=""JOB"",COUNTA(RC[-24]:RC[-1]),"""")"
    Range("AD2").Select
    ActiveCell.FormulaR1C1 = "=IF(N(RC[-1]),SUM(R2C[-1]:RC[-1])-RC[-1]+1,"""")"
    Range("AE2").Select
    ActiveCell.FormulaR1C1 = _
        "=IF(ROWS(R2C:RC)>SUM(C[-2]),"""",INDEX(C[-29],MATCH(ROWS(R2C:RC),C[-1])+1))"
    Range("AF2").Select
    ActiveCell.FormulaR1C1 = _
        "=IF(RC[-1]="""","""",INDEX(INDEX(R2C5:R96C28,MATCH(ROWS(R2C:RC),R2C30:R1000C[-2]),0),COUNTIF(R2C[-1]:RC[-1],RC[-1])))"
    Range("AC2:AF2").Select
    Selection.AutoFill Destination:=Range("AC2:AF3999"), Type:=xlFillDefault
    Range("AC2:AF3999").Select
    Columns("AC:AF").Select
    Selection.Copy
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Application.CutCopyMode = False
End Sub