Copy Paste Formula

[vmjan02]

I have formulas In cells I14, K14, L14, M14, N14 and O14 (All the formulas are different).

Is there a code where it will copy the formula till last non blank cell in Column H (in this record starts from H14).

I tried below code, but it is taking lot of time. Is there any other way as the formula is not limited to O14 , it is till CS14
Just need a different way as the execution of code is faster.

With Worksheets("Intraday -EOD Stats")
    With .Range("I14:I" & .Range("H" & .Rows.Count).End(xlUp).Row)
        .Formula = "=IF($H14<=$D$12,SUMIFS(INDIRECT($A$6&$B$5),INDIRECT($A$6&$C$5),$C14,INDIRECT($A$6&$D$5),$H14))"
        .Value = .Value
        '.NumberFormat = "DD-MM-YYYY"
     End With
End With

With Worksheets("Intraday -EOD Stats")
    With .Range("K14:K" & .Range("H" & .Rows.Count).End(xlUp).Row)
        .Formula = "=SUMIFS(INDIRECT($A$2&K$1),INDIRECT($A$2&$B$2),$C14,INDIRECT($A$2&$C$2),$G14,INDIRECT($A$2&$D$2),$H14)"
        .Value = .Value
        '.NumberFormat = "DD-MM-YYYY"
     End With
End With

With Worksheets("Intraday -EOD Stats")
    With .Range("L14:L" & .Range("H" & .Rows.Count).End(xlUp).Row)
        .Formula = "=SUMIFS(INDIRECT($A$2&L$1),INDIRECT($A$2&$B$2),$C14,INDIRECT($A$2&$C$2),$G14,INDIRECT($A$2&$D$2),$H14)"
        .Value = .Value
        '.NumberFormat = "DD-MM-YYYY"
     End With
End With

 

[Herakles]

So what is taking the time?

The writing the formula to the cells or the calculation?

Try turning automatic calculation off.

Application.Calculation = xlManual

and then on again at the end of the code

Application.Calculation = xlAutomatic

You will need to calculate the formulas before substituting the range with the result of the formula.

Application.Calculation = xlManual

With Worksheets("Intraday -EOD Stats")
    With .Range("I14:I" & .Range("H" & .Rows.Count).End(xlUp).Row)
        .Formula = "=IF($H14<=$D$12,SUMIFS(INDIRECT($A$6&$B$5),INDIRECT($A$6&$C$5),$C14,INDIRECT($A$6&$D$5),$H14))"
        .Calculate
        .Value = .Value
        '.NumberFormat = "DD-MM-YYYY"
     End With
End With

With Worksheets("Intraday -EOD Stats")
    With .Range("K14:K" & .Range("H" & .Rows.Count).End(xlUp).Row)
        .Formula = "=SUMIFS(INDIRECT($A$2&K$1),INDIRECT($A$2&$B$2),$C14,INDIRECT($A$2&$C$2),$G14,INDIRECT($A$2&$D$2),$H14)"
        .Calculate
        .Value = .Value
        '.NumberFormat = "DD-MM-YYYY"
     End With
End With

With Worksheets("Intraday -EOD Stats")
    With .Range("L14:L" & .Range("H" & .Rows.Count).End(xlUp).Row)
        .Formula = "=SUMIFS(INDIRECT($A$2&L$1),INDIRECT($A$2&$B$2),$C14,INDIRECT($A$2&$C$2),$G14,INDIRECT($A$2&$D$2),$H14)"
        .Calculate
        .Value = .Value
        '.NumberFormat = "DD-MM-YYYY"
     End With
End With

Application.Calculation = xlAutomatic