Performance lag issue

Giganews Newsgroups
Subject: Performance lag issue
Posted by:  Living the Dream (noodnu…@gmail.com)
Date: Tue, 13 Feb 2018

Hi Team

I was given a workbook with approx. 100,000 rows in total which they want to draw demographics from over there network which has a hamster treadmill for speed.. :)

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.

I decided to code it and came up with this and by all accounts the following does what I need it to do, albeit at a snails pace like almost 4 mins for 500 rows when I broke into the code as it was taking way too long.

In-as-much as it will most likely not make much sense without any file or data , but! I am hoping maybe you guys can see if there is a simplified approach that could turn this Model -T into a Ferrari.. lol.

As always

Many thanks in advanced.
Kind regards
Mark.

Sub Process_Me()

    With Application
        .ScreenUpdating = False
        .EnableEvents = False
    End With

    Dim myWbook As Workbook
    Dim myRange As Range, c As Range

    Set myWbook = ThisWorkbook
    Set myRange = Sheets("Data").Range("L2:L10000")

    For Each c In myRange
        If Not c = "" Then
            With c.Offset(, 1)
                .FormulaR1C1 = "=IF(RC[-3]="""","""",(RC[-3]-INT(RC[-3])))"
                .Value = .Value
            End With
            With c.Offset(, 2)
                .FormulaR1C1 = "=IF(RC[-4]="""","""",IF(OR(RC5=RC33, RC5=RC34, 1),2))"
                .Value = .Value
            End With
            With c.Offset(, 3)
                .FormulaR1C1 = "=IF(AND(RC14=2,RC13<0.25),(RC13+0.5),(RC13))"
                .Value = .Value
            End With
            With c.Offset(, 4)
                .FormulaR1C1 = "=RC15"
                .Value = .Value
            End With
            With c.Offset(, 5)
                .FormulaR1C1 = "=IF(RC11="""","""",RC11-INT(RC11))"
                .Value = .Value
            End With
            With c.Offset(, 6)
                .FormulaR1C1 = "=IF(RC15="""","""",IF(RC17>RC15,RC17-RC15,RC15-RC17))"
                .Value = .Value
            End With
            With c.Offset(, 7)
                .FormulaR1C1 = "=IF(RC16="""","""",IF(RC17>RC16,""LATE"",IF(RC17<RC16,""EARLY"",""ON TIME"")))"
                .Value = .Value
            End With
            With c.Offset(, 8)
                .FormulaR1C1 = "=IF(RC[-14]="""","""",IF(AND(RC19=""LATE"",(RC17-RC16<0.0208)),""ON TIME"",IF(RC17<RC16,""EARLY"",IF(RC17=RC16,""ON TIME"",""LATE""))))"
                .Value = .Value
            End With
            With c.Offset(, 9)
                .FormulaR1C1 = "=IF(RC12="""","""",TIME(HOUR(RC12),MINUTE(RC12),SECOND(RC12)))"
                .Value = .Value
            End With
            With c.Offset(, 10)
                .FormulaR1C1 = "=IF(RC9="""","""",RC9-1)"
                .Value = .Value
            End With
            With c.Offset(, 11)
                .FormulaR1C1 = "=IF(COUNTIFS(RC11:RC11,RC11,RC8:RC8,RC8,RC17:RC17,RC17)=1,1,"""")"
                .Value = .Value
            End With
            With c.Offset(, 12)
                .FormulaR1C1 = "=SUMIFS(C[-15]:C[-15],C[-16]:C[-16],RC[-16],C[-13]:C[-13],RC[-13])"
                .Value = .Value
            End With
            With c.Offset(, 13)
                .FormulaR1C1 = "=IF(RC[-2]="""","""",((RC[-2]*RC[-3])-1))"
                .Value = .Value
            End With
            With c.Offset(, 14)
                .FormulaR1C1 = "=IF(RC[-3]<>1,0,IF(RC[-1]>24,23,RC[-1]))"
                .Value = .Value
            End With
            With c.Offset(, 15)
                .FormulaR1C1 = "=IF(RC[-1]>0,15,0)"
                .Value = .Value
            End With
            With c.Offset(, 16)
                .FormulaR1C1 = "=IF(ISERROR(RC[-4]*2+RC[-1]),0,(RC[-4]*2+RC[-1]))"
                .Value = .Value
            End With
            With c.Offset(, 17)
                .FormulaR1C1 = "=IF(RC[-1]=0,0,(RC[-1]/1440))"
                .Value = .Value
            End With
            With c.Offset(, 18)
                .FormulaR1C1 = "=IF(RC[-7]<>1,0,IF(RC[-14]>RC[-9],0,IF(RC[-13]<RC[-14],RC[-9]-RC[-14],RC[-9]-RC[-13])))"
                .Value = .Value
            End With
            With c.Offset(, 19)
                .FormulaR1C1 = "=IF(RC[-8]<>1,0,IF(RC[-1]>RC[-2],0,IF(RC[-1]-RC[-2],0)))"
                .Value = .Value
            End With
            With c.Offset(, 20)
                .FormulaR1C1 = "=IF(RC12="""","""",TRIM(RC5))"
                .Value = .Value
            End With

        Else: Exit Sub

        End If
    Next c

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

End Sub

Replies