Performance – VBA code optimization (loops)

I added a timer to my code and the bottleneck occurs when I traverse 47 rows and input data from a dictionary that I loaded with values.

Since I use these files for many different things, I have configured public variables to avoid configuring them for each new code.

Timer statistics:

3.4453125 Open the customer database, determine in which account we are
3.484375 Find the last 4 weeks and order range
7.31640625 Adding columns and forming
7.61328125 during the last 4 weeks, add the station space and the P & L data to the dictionary
7.6484375 Find the cumulative range, add space and P & L for 2019
100.90234375 by adding dictionary data to the order file

Anyway, below is the code …

Sub Orders_Historicals_autofilterdict ()

Dark start as double
start = timer

& # 39; & # 39; --------------------------------------
Static variables
& # 39; & # 39; --------------------------------------
Defined Calls Variables

Dim orderFile As Variant
Dim orderStart As Long
Dim orderEnd As Long

Dim clientdataFile As Variant

Dim internalize file as variant
Dim dateStart As Long
Dim station Begin as long
Dim stationEnd as long

Dim currentStation as a chain
Dim currentWeek As String

Dim dictData As New Scripting.Dictionary
Dim stationclearanceData As New Scripting.Dictionary
Dim stationplData As New Scripting.Dictionary

Dim key as variant

Dim fileOnly As String
Dim networkOnly As String

Dim i so long
Dim w so long
Dim t so long

Dim PlTotal as long
Dark clear total total while
Dim stationHash As String

& # 39; & # 39; --------------------------------------
& # 39; & # 39; Dictionary of the abbreviations of the order
& # 39; & # 39; --------------------------------------
Application.ScreenUpdating = False

Set file order = ActiveWorkbook.ActiveSheet

Notebooks of work. Open client location

Set clientdataFile = ActiveWorkbook.Sheets (dan_location) & # 39; / Change sheet when used on a different computer

clientdataFile.Activate

For i = 1 To Cells (Rows.count, 1) .End (xlUp) .row
If dictData.Exists (Cells (i, clientOrder) .Value) Then
Otherwise: dictData.Add Cells (i, clientOrder) .Value, i
It will end if
Following

& # 39; & # 39; --------------------------------------
& # 39; & # 39; Determine account / network and open internal associate with order
& # 39; & # 39; --------------------------------------
orderFile.Activate

fileOnly = ActiveWorkbook.Name

fileOnly = Left (fileOnly, InStr (fileOnly, ".") - 1)

If InStr (fileOnly, 2)> 0 Or InStr (fileOnly, 3)> 0 Then
fileOnly = Left (fileOnly, Len (fileOnly) - 1)
It will end if

networkOnly = ActiveWorkbook.Name

networkOnly = Mid (networkOnly, InStr (networkOnly, "IO.") + 3)

networkOnly = Left (networkOnly, InStr (networkOnly, ".") - 1)

Workbooks.Open Filename: = clientdataFile.Cells (dictData (fileOnly), clientInternal) .Value

Set internalFile = ActiveWorkbook

internalFile.Sheets (WT_newWeek) .Activate

Debug.Print Timer - start and "Open client database, determine what account we are in"
& # 39; & # 39; --------------------------------------
& # 39; & # 39; Search last 4 dates and column header for orders
& # 39; & # 39; --------------------------------------
For i = 1 to 700
If Cells (i, 1) = WT_newWeek Then
dateStart = i
ElseIf cells (i, 1) = "Station" Then
stationStart = i + 1
Output for
It will end if
Following

For i = stationStart To 700
If the cells (i, 1) .Value = Cells (stationStart - 2, 1) .Value and "Total" then
stationEnd = i - 1
Output for
It will end if
Following

orderFile.Activate

For i = 1 to 700
If the cells (i, 1) = "Station" then
orderStart = i + 1
Output for
It will end if
Following

For i = orderStart To 700
If Len (Cells (i, 1)) = 0 And Len (Cells (i - 1, 1)) = 0 And Len (Cells (i - 2, 1)) = 0 Then
orderEnd = i - 3
Output for
It will end if
Following


Debug.Print Timer - start & "Find last 4 weeks and order range"

& # 39; & # 39; --------------------------------------
& # 39; & # 39; Add dates to order header and format
& # 39; & # 39; --------------------------------------

Cells (orderStart - 1, OF_buyAlgoColumn) = "Algorithm recommendation"
Cells (orderStart - 1, OF_totalplColumn) = "Total P & L"
Cells (orderStart - 1, OF_totalclearanceColumn) = "% of total settlement"

Cells (orderStart - 1, OF_clearanceColumn1) = internalFile.Sheets (WT_newWeek). Cells (dateStart, 1)
Cells (orderStart - 1, OF_clearanceColumn2) = internalFile.Sheets (WT_newWeek). Cells (dateStart - 1, 1)
Cells (orderStart - 1, OF_clearanceColumn3) = internalFile.Sheets (WT_newWeek). Cells (dateStart - 2, 1)
Cells (orderStart - 1, OF_clearanceColumn4) = internalFile.Sheets (WT_newWeek). Cells (dateStart - 3, 1)

Cells (orderStart - 1, OF_plColumn1) = internalFile.Sheets (WT_newWeek). Cells (dateStart, 1)
Cells (orderStart - 1, OF_plColumn2) = internalFile.Sheets (WT_newWeek). Cells (dateStart - 1, 1)
Cells (orderStart - 1, OF_plColumn3) = internalFile.Sheets (WT_newWeek). Cells (dateStart - 2, 1)
Cells (orderStart - 1, OF_plColumn4) = internalFile.Sheets (WT_newWeek). Cells (dateStart - 3, 1)

Range (Cells (orderStart - 2, OF_clearanceColumn1), Cells (orderStart - 2, OF_clearanceColumn4)) = "Settlement"
Range (Cells (orderStart - 2, OF_plColumn1), Cells (orderStart - 2, OF_plColumn4)) = "P & L"

Cells (orderStart - 1, OF_stationColumn) .Copy
Range (Cells (orderStart - 1, OF_buyAlgoColumn), Cells (orderStart - 1, OF_plColumn4)). PasteSpecial xlPasteFormats

Cells (orderStart, OF_stationColumn) .Copy
Range (Cells (orderStart - 2, OF_clearanceColumn1), Cells (orderStart - 2, OF_plColumn4)). PasteSpecial xlPasteFormats

Range (Cells (orderStart - 2, OF_buyAlgoColumn), Cells (orderEnd, OF_plColumn4)). HorizontalAlignment = xlCenter

Cells (orderStart, OF_stationColumn) .Copy
Range (Cells (orderStart, OF_buyAlgoColumn), Cells (orderEnd, OF_plColumn4)). PasteSpecial xlPasteFormats

Cells (orderStart, OF_totalColumn) .Copy
Range (Cells (orderStart, OF_plColumn1), Cells (orderEnd, OF_plColumn4)). PasteSpecial xlPasteFormats
Range (Cells (orderStart, OF_totalplColumn), Cells (orderEnd, OF_totalplColumn)). PasteSpecial xlPasteFormats

Range (Cells (orderStart, OF_totalclearanceCearance Column), Cells (orderEnd, OF_clearanceColumn4)). NumberFormat = "0%"

Range (Cells (orderStart - 2, OF_buyAlgoColumn), Cells (orderEnd, OF_plColumn4)). FormatConditions.Delete

Range (Columns (OF_buyAlgoColumn), Columns (OF_plColumn4)). AutoFit

Debug.Print Timer - start and "Add columns and format"

& # 39; & # 39; --------------------------------------
& # 39; & # 39; Add space and P & L by date to the dictionary
& # 39; & # 39; --------------------------------------

For i = OF_clearanceColumn1 To OF_clearanceColumn4

currentWeek = Cells (orderStart - 1, i) .Value

internalFile.Sheets (currentWeek) .Activate

For t = 1 to 700

If the cells (t, 1) = "Station" then
StationStart = t + 1
Output for
It will end if
Following

For t = stationStart To 700

If the cells (t, 1) .Value = Cells (stationStart - 2, 1) .Value and "Total" then
stationEnd = i - 1
Output for
It will end if

If stationclearanceData.Exists (Cells (t, WT_stationColumn) .Value & currentWeek) Then
Plus:
In error, resume next
stationclearanceData.Add Cells (t, WT_stationColumn) .Value & currentWeek, Cells (t, WT_mediaactColumn) .Value / Cells (t, WT_mediaestColumn) .Value
stationplData.Add Cells (t, WT_stationColumn) .Value & currentWeek, Cells (t, WT_profitColumn) .Value
It will end if

Following

orderFile.Activate

Following

Debug.Print Timer - start and "go through the last 4 weeks, add free station space and P & L data to the dictionary"

& # 39; & # 39; --------------------------------------
& # 39; & # 39; Add cumulative settlement and P & L to the dictionary
& # 39; & # 39; --------------------------------------

internalFile.Sheets ("Cumulative"). Activate

For t = 5 to 70000

If Cells (t, 1) = "" and Cells (t + 1, 1) = "" and Cells (t + 2, 1) = "" Then
stationEnd = t + 1
Output for
It will end if
Following

For t = 5 to park

If the cells (t, CT_yearColumn) = 2019 then

If stationclearanceData.Exists (Cells (t, CT_hashColumn) .Value) Then
Plus:
In error, resume next
stationclearanceData.Add Cells (t, CT_hashColumn) .Value, Cells (t, CT_clearanceColumn) .Value
stationplData.Add Cells (t, CT_hashColumn) .Value, Cells (t, CT_invoiceColumn) .Value - Cells (t, CT_actcostColumn) .Value
It will end if
It will end if
Following

Debug.Print Timer - start and "find cumulative range, add space and P & L for 2019"


orderFile.Activate

& # 39; & # 39; --------------------------------------
& # 39; & # 39; Go through the stations in the order file and update according to the dictionary values
& # 39; & # 39; --------------------------------------

For w = orderStart To orderEnd

If the cells (w, OF_stationColumn) <> "" then

If Cells (w, OF_stationColumn) <> Cells (w - 1, OF_stationColumn) Then

stationHash = Cells (w, OF_stationColumn) .Value & "" & Cells (w, OF_trafficColumn) .Value & "Total"

In error, resume next
Cells (w, OF_clearanceColumn1) = stationclearanceData (Cells (w, OF_stationColumn) .Value & Cells (orderStart - 1, OF_clearanceColumn1) .Value)
Cells (w, OF_clearanceColumn2) = stationclearanceData (Cells (w, OF_stationColumn) .Value & Cells (orderStart - 1, OF_clearanceColumn2) .Value)
Cells (w, OF_clearanceColumn3) = stationclearanceData (Cells (w, OF_stationColumn) .Value & Cells (orderStart - 1, OF_clearanceColumn3) .Value)
Cells (w, OF_clearanceColumn4) = stationclearanceData (Cells (w, OF_stationColumn) .Value & Cells (orderStart - 1, OF_clearanceColumn4) .Value)

Cells (w, OF_plColumn1) = stationplData (Cells (w, OF_stationColumn) .Value & Cells (orderStart - 1, OF_plColumn1) .Value)
Cells (w, OF_plColumn2) = stationplData (Cells (w, OF_stationColumn) .Value & Cells (orderStart - 1, OF_plColumn2) .Value)
Cells (w, OF_plColumn3) = stationplData (Cells (w, OF_stationColumn) .Value & Cells (orderStart - 1, OF_plColumn3) .Value)
Cells (w, OF_plColumn4) = stationplData (Cells (w, OF_stationColumn) .Value & Cells (orderStart - 1, OF_plColumn4) .Value)

Cells (w, OF_totalplColumn) = stationplData (stationHash)
Cells (w, OF_totalclearanceColumn) = stationclearanceData (stationHash)

It will end if

It will end if

Following

Debug.Print Timer - start and "add dictionary data to the order file"

clientdataFile.Activate

ActiveWorkbook.Close saveChanges: = False

Application.ScreenUpdating = True

Range (Cells (orderStart - 2, OF_buyAlgoColumn), Cells (orderEnd, OF_plColumn4)). HorizontalAlignment = xlCenter

MsgBox ("Complete purchase algorithm")


End Sub