CODEWITHSUNDEEP
excelvba
kobebryant
kobebryant

Reputation: 71

How to run this code faster

I have this code where the content is: filling formulas in cells that have no formula, then copy/paste values of entire row of those cells. I used 2 times "For Next", first for filling formula and the second to paste values 

Sub CDPSKoCongThuc()
Application.ScreenUpdating = False
Application.EnableEvents = False
Application.Calculation = xlCalculationManual
    Dim RowNKC As Integer
    RowNKC = Range("CuoiNKC").Row - 1
    Dim RowCDPS As Integer
    RowCDPS = Range("CuoiCDPS").Row - 1
    Dim i As Integer
    Dim x As Integer
    For i = 9 To RowCDPS
        If Cells(i, 9).HasFormula = False Then
            Cells(i, 7).FormulaR1C1 = "=SUMIF(NKC!R9C12:R" & RowNKC & "C12,CDPS!RC[-6],NKC!R9C15:R" & RowNKC & "C15)"
            Cells(i, 8).FormulaR1C1 = "=SUMIF(NKC!R9C13:R" & RowNKC & "C13,CDPS!RC[-7],NKC!R9C15:R" & RowNKC & "C15)"
            Cells(i, 9).FormulaR1C1 = "=ROUND(SUMIF(NKC!R9C12:R" & RowNKC & "C12,CDPS!RC[-8],NKC!R9C14:R" & RowNKC & "C14),0)"
            Cells(i, 10).FormulaR1C1 = "=ROUND(SUMIF(NKC!R9C13:R" & RowNKC & "C13,CDPS!RC[-9],NKC!R9C14:R" & RowNKC & "C14),0)"

            Cells(i, 11).FormulaR1C1 = "=MAX(RC[-8]+RC[-4]-RC[-3]-RC[-7],0)"
            Cells(i, 12).FormulaR1C1 = "=MAX(RC[-4]+RC[-8]-RC[-5]-RC[-9],0)"
            Cells(i, 13).FormulaR1C1 = "=ROUND(MAX(RC[-8]+RC[-4]-RC[-7]-RC[-3],0),0)"
            Cells(i, 14).FormulaR1C1 = "=ROUND(MAX(RC[-4]+RC[-8]-RC[-5]-RC[-9],0),0)"
        End If
    Next i

'Paste Values Formula

    For x = 9 To RowCDPS
        If Cells(x, 9).Font.Bold = False And Len(Cells(x, 1).Value) > 3 Then
            Rows(x).EntireRow.Copy
            Rows(x).PasteSpecial xlPasteValues
        End If
    Next x

Application.Calculation = xlCalculationAutomatic
Application.EnableEvents = True
Application.ScreenUpdating = True
End Sub

Upvotes: 0

Views: 85

Answers (1)

Steven Martin
Steven Martin

Reputation: 3272

I'm not going to write them all but you could use VBA to do the caculations and put the value in the cell so you don't have to paste the values or work with excel calculations at all.

For example...

  Cells(i, 11).FormulaR1C1 = "=MAX(RC[-8]+RC[-4]-RC[-3]-RC[-7],0)"

would be

  Cells(i, 11) = Application.Max(Cells(i, 3) + Cells(i, 7) - Cells(i,8) -Cells(i, 4), 0)

You should also make a variable for each sheet and use them to reference the cells so that its clearer to read

Dim shCDPS as Worksheet
Dim shNKC as worksheet

Set shCDPS = Sheets("CDPS")  
Set shNKC = Sheets("NKC")

Then this formula 

 Cells(i, 7).FormulaR1C1 = "=SUMIF(NKC!R9C12:R" & RowNKC & "C12,CDPS!RC[-6],NKC!R9C15:R" & RowNKC & "C15)"

would become

shCDPS.Cells(i, 7) = Application.SumIf(shNKC.Range("L9:L" & RowNKC) , shCDPS.Range("A" & i), shNKC.Range("O15:O" & RowNKC))

Upvotes: 1

Related Questions