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(RC17RC15,RC17-RC15,RC15-RC17))"
.Value = .Value
End With
With c.Offset(, 7)
.FormulaR1C1 = "=IF(RC16="""","""",IF(RC17RC16,""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),S ECOND(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