Groups | Search | Server Info | Keyboard shortcuts | Login | Register [http] [https] [nntp] [nntps]

Groups > microsoft.public.excel.programming > #110492

Re: Performance lag issue

Show all headers | View raw

Hi Mark,

Am Tue, 13 Feb 2018 05:12:42 -0800 (PST) schrieb Living the Dream:

> I actually used cell formulas to begin with and noticed two things happen:
> 
> 1. the file ballooned to a an unmanageable size. ( should be a Database IMO ).
> 2. It literally ground to a halt the more rows I copied the formula to.
> 

try:

Sub Process_Me()
Dim LRow As Long
Dim iCalc As Integer

    With Application
        .ScreenUpdating = False
        .EnableEvents = False
        iCalc = .Calculation
        .Calculation = xlCalculationManual
    End With

    Dim myWbook As Workbook
    
    Set myWbook = ThisWorkbook
    With Sheets("Data")
      LRow = .Cells(.Rows.Count, "L").End(xlUp).Row
      
         With .Cells(2, 13).Resize(LRow - 1)
            .Formula = "=IF(J2="""","""",(J2-INT(J2)))"
           .Value = .Value
         End With
         With .Cells(2, 14).Resize(LRow - 1)
            .Formula = "=IF(J2="""","""",IF(OR($E2=$AG2, $E2=$AH2, 1),2))"
            .Value = .Value
         End With
         With .Cells(2, 15).Resize(LRow - 1)
            .Formula = "=IF(AND($N2=2,$M2<0.25),($M2+0.5),($M2))"
            .Value = .Value
         End With
         With .Cells(2, 16).Resize(LRow - 1)
            .Formula = "=$O2"
            .Value = .Value
         End With
         With .Cells(2, 17).Resize(LRow - 1)
            .Formula = "=IF($K2="""","""",$K2-INT($K2))"
            .Value = .Value
         End With
         With .Cells(2, 18).Resize(LRow - 1)
            .Formula = "=IF($O2="""","""",IF($Q2>$O2,$Q2-$O2,$O2-$Q2))"
            .Value = .Value
         End With
         With .Cells(2, 19).Resize(LRow - 1)
            .Formula = "=IF($P2="""","""",IF($Q2>$P2,""LATE"",IF($Q2<$P2,""EARLY"",""ON TIME"")))"
            .Value = .Value
         End With
         With .Cells(2, 20).Resize(LRow - 1)
            .Formula = "=IF(F2="""","""",IF(AND($S2=""LATE"",($Q2-$P2<0.0208)),""ON TIME"",IF($Q2<$P2,""EARLY"",IF($Q2=$P2,""ON TIME"",""LATE""))))"
            .Value = .Value
         End With
         With .Cells(2, 21).Resize(LRow - 1)
            .Formula = "=IF($L2="""","""",TIME(HOUR($L2),MINUTE($L2),SECOND($L2)))"
            .Value = .Value
         End With
         With .Cells(2, 22).Resize(LRow - 1)
            .Formula = "=IF($I2="""","""",$I2-1)"
            .Value = .Value
         End With
         With .Cells(2, 23).Resize(LRow - 1)
            .Formula = "=IF(COUNTIFS($K2:$K2,$K2,$H2:$H2,$H2,$Q2:$Q2,$Q2)=1,1,"""")"
            .Value = .Value
         End With
         With .Cells(2, 24).Resize(LRow - 1)
            .Formula = "=SUMIFS(I:I,H:H,H2,K:K,K2)"
            .Value = .Value
         End With
         With .Cells(2, 25).Resize(LRow - 1)
            .Formula = "=IF(W2="""","""",((W2*V2)-1))"
            .Value = .Value
         End With
         With .Cells(2, 26).Resize(LRow - 1)
            .Formula = "=IF(W2<>1,0,IF(Y2>24,23,Y2))"
            .Value = .Value
         End With
         With .Cells(2, 27).Resize(LRow - 1)
            .Formula = "=IF(Z2>0,15,0)"
            .Value = .Value
         End With
         With .Cells(2, 28).Resize(LRow - 1)
            .Formula = "=IF(ISERROR(X2*2+AA2),0,(X2*2+AA2))"
            .Value = .Value
         End With
         With .Cells(2, 29).Resize(LRow - 1)
            .Formula = "=IF(AB2=0,0,(AB2/1440))"
            .Value = .Value
         End With
         With .Cells(2, 30).Resize(LRow - 1)
            .Formula = "=IF(W2<>1,0,IF(P2>U2,0,IF(Q2<P2,U2-P2,U2-Q2)))"
            .Value = .Value
         End With
         With .Cells(2, 31).Resize(LRow - 1)
            .Formula = "=IF(W2<>1,0,IF(AD2>AC2,0,IF(AD2-AC2,0)))"
            .Value = .Value
         End With
         With .Cells(2, 32).Resize(LRow - 1)
            .Formula = "=IF($L2="""","""",TRIM($E2))"
            .Value = .Value
         End With
   End With

    With Application
        .ScreenUpdating = True
        .EnableEvents = True
        .Calculation = iCalc
    End With

End Sub


Regards
Claus B.
-- 
Windows10
Office 2016

Back to microsoft.public.excel.programming | Previous | Next — Previous in thread | Next in thread | Find similar | Unroll thread

Thread

Performance lag issue Living the Dream <noodnutt@gmail.com> - 2018-02-13 05:12 -0800
  Re: Performance lag issue Claus Busch <claus_busch@t-online.de> - 2018-02-13 15:50 +0100
    Re: Performance lag issue Claus Busch <claus_busch@t-online.de> - 2018-02-13 20:46 +0100
      Re: Performance lag issue Living the Dream <noodnutt@gmail.com> - 2018-02-14 03:27 -0800
        Re: Performance lag issue GS <gs@v.invalid> - 2018-02-14 06:31 -0500
          Re: Performance lag issue GS <gs@v.invalid> - 2018-02-14 06:34 -0500
  Re: Performance lag issue GS <gs@v.invalid> - 2018-02-13 11:20 -0500
  Re: Performance lag issue GS <gs@v.invalid> - 2018-02-13 11:34 -0500

csiph-web