RawlinsCross
Active Member
- Joined
- Sep 9, 2016
- Messages
- 437
So I have several sub procedures in an excel application, one of which uses a few For Loops to enter data into various dictionaries. In an older version this particular sub procedure took 0.102 seconds to execute. Then I added a worksheet to the workbook which contains a lot of data (number of cells containing data = 2.4 million). Now this sub procedure takes 11.52 seconds to execute even though the procedure really doesn't reference this new worksheet. The worksheet on which the For Loop references is called "DataWorkup" and this new data-heavy is called "Harvested". You can see in the code that "Harvested" is not referenced - why would this code slow down in that case?
The iteration count in these For Loops range from 7 to 12 so they're quite short. So I'm not sure why this sub-procedure would take any longer to run. Any thoughts on what I can do here?
The iteration count in these For Loops range from 7 to 12 so they're quite short. So I'm not sure why this sub-procedure would take any longer to run. Any thoughts on what I can do here?
VBA Code:
Private Sub FileToSpreadSheet()
Dim wSht As Worksheet
Dim rRange As Range
Dim i As Long
Dim sActivity As String
Dim Add1 As Long
Dim Add2 As Long
Dim StartTime As Double
Dim SecondsElapsed As Double
StartTime = Timer
Set wSht = ThisWorkbook.Worksheets("DataWorkup")
wSht.Unprotect "Tankhouse123"
'Transfer "Crane Related-West" (GOOD)
Set rRange = wSht.Range("WestCraneWater")
For i = 3 To rRange.Rows.Count
sActivity = rRange.Cells(i, 1).Value
rRange.Cells(i, 4).Value = moWestCraneDict(sActivity)
Next i
'Transfer "Non-Crane Related-West" (GOOD)
Set rRange = wSht.Range("WestNonCraneWater")
For i = 3 To rRange.Rows.Count
sActivity = rRange.Cells(i, 1).Value
rRange.Cells(i, 4).Value = moWestNonCraneDict(sActivity)
Next i
'Transfer "West Both" (GOOD)
Set rRange = wSht.Range("WestBothWater")
For i = 3 To rRange.Rows.Count
sActivity = rRange.Cells(i, 1).Value
'This tag will either be in moWestCraneDict or moWestNonCraneDict
If moWestCraneDict.Exists(sActivity) Then
rRange.Cells(i, 4) = moWestCraneDict(sActivity)
Else
rRange.Cells(i, 4) = moWestNonCraneDict(sActivity)
End If
Next i
'Transfer "Crane Related-East" (GOOD)
Set rRange = wSht.Range("EastCraneWater")
For i = 3 To rRange.Rows.Count
sActivity = rRange.Cells(i, 1).Value
rRange.Cells(i, 4).Value = moEastCraneDict(sActivity)
Next i
'Transfer "Non-Crane Related-East" (GOOD)
Set rRange = wSht.Range("EastNonCraneWater")
For i = 3 To rRange.Rows.Count
sActivity = rRange.Cells(i, 1).Value
rRange.Cells(i, 4).Value = moEastNonCraneDict(sActivity)
Next i
'Transfer "East Both" (GOOD)
Set rRange = wSht.Range("EastBothWater")
For i = 3 To rRange.Rows.Count
sActivity = rRange.Cells(i, 1).Value
'This tag will either be in moEastCraneDict or moEastNonCraneDict
If moEastCraneDict.Exists(sActivity) Then
rRange.Cells(i, 4) = moEastCraneDict(sActivity)
Else
rRange.Cells(i, 4) = moEastNonCraneDict(sActivity)
End If
Next i
'Transfer "Both Crane" (GOOD)
Set rRange = wSht.Range("BothCraneWater")
For i = 3 To rRange.Rows.Count
sActivity = rRange.Cells(i, 1).Value
'These tags will be in moWestCraneDict and moEastCraneDict
rRange.Cells(i, 4) = moWestCraneDict(sActivity) + moEastCraneDict(sActivity)
Next i
'Transfer "Both NonCrane" (GOOD)
Set rRange = wSht.Range("BothNonCraneWater")
For i = 3 To rRange.Rows.Count
sActivity = rRange.Cells(i, 1).Value
'These tags will be in moWestNonCraneDict and moEastNonCraneDict
rRange.Cells(i, 4) = moWestNonCraneDict(sActivity) + moEastNonCraneDict(sActivity)
Next i
'Tranfer "Both Both" (GOOD)
Set rRange = wSht.Range("BothBothWater")
For i = 3 To rRange.Rows.Count
sActivity = rRange.Cells(i, 1).Value
'This tag will either be in moEastCraneDict or moEastNonCraneDict and their West counterpoints
If moEastCraneDict.Exists(sActivity) Then
rRange.Cells(i, 4) = moEastCraneDict(sActivity) + moWestCraneDict(sActivity)
Else
rRange.Cells(i, 4) = moEastNonCraneDict(sActivity) + moWestNonCraneDict(sActivity)
End If
Next i
'Sort the ranges
wSht.Range("WestCraneWaterSort").Sort Key1:=wSht.Range("R3"), Order1:=xlDescending, Header:=xlNo
wSht.Range("WestNonCraneWaterSort").Sort Key1:=wSht.Range("X3"), Order1:=xlDescending, Header:=xlNo
wSht.Range("EastCraneWaterSort").Sort Key1:=wSht.Range("AM3"), Order1:=xlDescending, Header:=xlNo
wSht.Range("EastNonCraneWaterSort").Sort Key1:=wSht.Range("AS3"), Order1:=xlDescending, Header:=xlNo
wSht.Range("WestBothWaterSort").Sort Key1:=wSht.Range("AE3"), Order1:=xlDescending, Header:=xlNo
wSht.Range("EastBothWaterSort").Sort Key1:=wSht.Range("AZ3"), Order1:=xlDescending, Header:=xlNo
wSht.Range("BothCraneWaterSort").Sort Key1:=wSht.Range("BG3"), Order1:=xlDescending, Header:=xlNo
wSht.Range("BothNonCraneWaterSort").Sort Key1:=wSht.Range("BM3"), Order1:=xlDescending, Header:=xlNo
wSht.Range("BothBothWaterSort").Sort Key1:=wSht.Range("BT3"), Order1:=xlDescending, Header:=xlNo
SecondsElapsed = Round(Timer - StartTime, 3)
Debug.Print SecondsElapsed
End Sub