Excel VBA Explanation/Optimisation Needed

I am a self taught VBA user. I was asked to look at the code from a tool which has stopped working due to a data overflow and "fix" it. I was told the code was 'optimised' so that it took only 2 hours to run instead of four. They did this by remove loops.

I didn't have an issue with my 64-bit Excel, but another user stepped through it for me and the below lines prompted the 'out of memory' error.

Calculations are set to manual and the screen is frozen at the beginning of the code.

In my limited experience I've had faster results doing a loop and avoiding setting formulas in the document. There are around 500,000 lines of data currently.

Would I be better off changing it to a loop? Or would that add hours onto the computation time? I'm happy to post the full code if anyone wants to see it, but it is not annotated and none of the variables are defined so it is a bit of a mess. I'm cleaning it up as I decipher the code.

Sheets("Main Tab").Range("O2:O" & LastRow).Formula = "=IF(AND(N2>IF(VLOOKUP(CONCATENATE(E2,C2,D2,CalculateWeek),Velocity!J$2:N$" & VelocityLastRow & ",1,TRUE)=CONCATENATE(E2,C2,D2,CalculateWeek), VLOOKUP(CONCATENATE(E2,C2,D2,CalculateWeek),Velocity!J$2:N$" & VelocityLastRow & ",4,TRUE), ""Missing""), N2<>""""),IF(VLOOKUP(CONCATENATE(E2,C2,D2,CalculateWeek),Velocity!J$2:N$" & VelocityLastRow & ",1,TRUE)=CONCATENATE(E2,C2,D2,CalculateWeek), VLOOKUP(CONCATENATE(E2,C2,D2,CalculateWeek),Velocity!J$2:N$" & VelocityLastRow & ",4,TRUE), ""Missing""),N2)"
Sheets("Main Tab").Range("I2:O" & LastRow).Value = Sheets("Main Tab").Range("I2:O" & LastRow).Value


Full code (Calculate_Click is what is causing the issue):

 Sub Clear_Click()

DisableOptimize
UnfilterAll
Application.Calculation = xlCalculationManual
Application.EnableEvents = False

MainTabLastColum = "AU"

Sheets("Main Tab").AutoFilterMode = False
Sheets("Microstrategy Data").AutoFilterMode = False
Sheets("Velocity").AutoFilterMode = False

LastRow = Sheets("Order Upload").Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
If LastRow > 1 Then
End If

LastRow = Sheets("SKU-DC Summary").Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
If LastRow > 1 Then
Sheets("SKU-DC Summary").Range("A2:S" & LastRow).ClearContents
End If

LastRow = Sheets("Main Tab").Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
If LastRow > 1 Then
Sheets("Main Tab").Range("A2:" & MainTabLastColum & LastRow).ClearContents
End If

LastRow = Sheets("Microstrategy Data").Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
If LastRow > 1 Then
Sheets("Microstrategy Data").Range("A2:H" & LastRow).ClearContents
End If

LastRow = Sheets("Velocity").Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
If LastRow > 1 Then
Sheets("Velocity").Range("J2:N" & LastRow).ClearContents
Sheets("Velocity").Range("V2:V" & LastRow).ClearContents
End If

MainTabLastColum = Null
QuantityLastColumn = Null
LastRow = Null

End Sub

Dim StartTime As Double
Dim SecondsElapsed As Double
StartTime = Timer

DisableOptimize

LastRowMsCopyTo = Sheets("Microstrategy Data").Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
LastRowMsCopyFrom = Sheets("Data Input Microstrategy").Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row

Call CopyValues(Sheets("Data Input Microstrategy").Range("A7:A" & LastRowMsCopyFrom), Sheets("Microstrategy Data").Range("A2:A" & LastRowMsCopyFrom))
Call CopyValues(Sheets("Data Input Microstrategy").Range("B7:B" & LastRowMsCopyFrom), Sheets("Microstrategy Data").Range("B2:B" & LastRowMsCopyFrom))
Call CopyValues(Sheets("Data Input Microstrategy").Range("C7:C" & LastRowMsCopyFrom), Sheets("Microstrategy Data").Range("C2:C" & LastRowMsCopyFrom))
Call CopyValues(Sheets("Data Input Microstrategy").Range("D7:D" & LastRowMsCopyFrom), Sheets("Microstrategy Data").Range("E2:E" & LastRowMsCopyFrom))
Call CopyValues(Sheets("Data Input Microstrategy").Range("E7:E" & LastRowMsCopyFrom), Sheets("Microstrategy Data").Range("F2:F" & LastRowMsCopyFrom))
Call CopyValues(Sheets("Data Input Microstrategy").Range("F7:F" & LastRowMsCopyFrom), Sheets("Microstrategy Data").Range("G2:G" & LastRowMsCopyFrom))
Call CopyValues(Sheets("Data Input Microstrategy").Range("G7:G" & LastRowMsCopyFrom), Sheets("Microstrategy Data").Range("H2:H" & LastRowMsCopyFrom))

LastRowMsCopyTo = Sheets("Microstrategy Data").Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row

Sheets("Microstrategy Data").Range("D2:D" & LastRowMsCopyTo).Formula = "=Index('Data Input by Account'!$D:$D,match($A2,'Data Input by Account'!$A:$A,false))" Worksheets("Microstrategy Data").UsedRange.Columns("D").Calculate Call CopyValues(Sheets("Microstrategy Data").Range("D2:D" & LastRowMsCopyFrom), Sheets("Microstrategy Data").Range("D2:D" & LastRowMsCopyFrom)) Call CopyValues(Sheets("Microstrategy Data").Range("A2:A" & LastRowMsCopyFrom), Sheets("Main Tab").Range("A2:A" & LastRowMsCopyFrom)) Call CopyValues(Sheets("Microstrategy Data").Range("B2:B" & LastRowMsCopyFrom), Sheets("Main Tab").Range("B2:B" & LastRowMsCopyFrom)) Call CopyValues(Sheets("Microstrategy Data").Range("C2:C" & LastRowMsCopyFrom), Sheets("Main Tab").Range("C2:C" & LastRowMsCopyFrom)) Call CopyValues(Sheets("Microstrategy Data").Range("D2:D" & LastRowMsCopyFrom), Sheets("Main Tab").Range("D2:D" & LastRowMsCopyFrom)) Call CopyValues(Sheets("Microstrategy Data").Range("E2:E" & LastRowMsCopyFrom), Sheets("Main Tab").Range("E2:E" & LastRowMsCopyFrom)) Call CopyValues(Sheets("Microstrategy Data").Range("F2:F" & LastRowMsCopyFrom), Sheets("Main Tab").Range("F2:F" & LastRowMsCopyFrom)) Call CopyValues(Sheets("Microstrategy Data").Range("G2:G" & LastRowMsCopyFrom), Sheets("Main Tab").Range("G2:G" & LastRowMsCopyFrom)) Call CopyValues(Sheets("Microstrategy Data").Range("H2:H" & LastRowMsCopyFrom), Sheets("Main Tab").Range("H2:H" & LastRowMsCopyFrom)) LastRow = Sheets("Main Tab").Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row Application.CutCopyMode = False LastRowMsCopyTo = Null LastRowMsCopyFrom = Null LastRow = Null End Sub Sub CopyValues(rngSource As Range, rngTarget As Range) rngTarget.Resize(rngSource.Rows.Count, rngSource.Columns.Count).Value = rngSource.Value End Sub Sub Calculate_Click() MainTabLastColum = "AU" QuantityLastColumn = "O" Worksheets("Main Tab").Select DisableOptimize LastRowAvail = Sheets("Quantity Available").Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row Sheets("Quantity Available").Range("E2:E" & LastRowAvail).Formula = "=CONCATENATE(A2,B2)" Sheets("Quantity Available").Range("F2:F" & LastRowAvail).Formula = "=CONCATENATE(A2,Upper(B2),C2)" Sheets("Quantity Available").Range("E2:F" & LastRowAvail).Value = Sheets("Quantity Available").Range("E2:F" & LastRowAvail).Value ActiveWorkbook.Worksheets("Quantity Available").Sort.SortFields.Clear ActiveWorkbook.Worksheets("Quantity Available").Sort.SortFields.Add Key:=Range( _ "C2:C" & LastRowAvail), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:= _ xlSortNormal ActiveWorkbook.Worksheets("Quantity Available").Sort.SortFields.Add Key:=Range( _ "E2:E" & LastRowAvail), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:= _ xlSortNormal With ActiveWorkbook.Worksheets("Quantity Available").Sort .SetRange Range("A1:F" & LastRowAvail) .Header = xlYes .MatchCase = False .Orientation = xlTopToBottom .Apply End With LastRowData = Sheets("Data Input by Account").Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row Sheets("Data Input by Account").Range("C2:C" & LastRowData).Formula = "=VLOOKUP(B7,Table5,2,FALSE)" Sheets("Data Input by Account").Range("C2:C" & LastRowData).Value = Sheets("Data Input by Account").Range("C2:C" & LastRowData).Value ActiveWorkbook.Worksheets("Data Input by Account").Sort.SortFields.Clear ActiveWorkbook.Worksheets("Data Input by Account").Sort.SortFields.Add Key:=Range( _ "A2:A" & LastRowData), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:= _ xlSortNormal With ActiveWorkbook.Worksheets("Data Input by Account").Sort .SetRange Range("A1:D" & LastRowData) .Header = xlYes .MatchCase = False .Orientation = xlTopToBottom .Apply End With VelocityLastRow = Sheets("Velocity").Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row Sheets("Velocity").Range("J2:J" & VelocityLastRow).Formula = "=CONCATENATE(A2,D2,E2,I2)" Sheets("Velocity").Range("K2:K" & VelocityLastRow).Formula = "=F2" Sheets("Velocity").Range("L2:L" & VelocityLastRow).Formula = "=G2" Sheets("Velocity").Range("M2:M" & VelocityLastRow).Formula = "=H2" Sheets("Velocity").Range("N2:N" & VelocityLastRow).Formula = "=C2" Sheets("Velocity").Range("J2:N" & VelocityLastRow).Value = Sheets("Velocity").Range("J2:N" & VelocityLastRow).Value ActiveWorkbook.Worksheets("Velocity").Sort.SortFields.Clear ActiveWorkbook.Worksheets("Velocity").Sort.SortFields.Add Key:=Range( _ "J2:J" & VelocityLastRow), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:= _ xlSortNormal With ActiveWorkbook.Worksheets("Velocity").Sort .SetRange Range("A1:N" & VelocityLastRow) .Header = xlYes .MatchCase = False .Orientation = xlTopToBottom .Apply End With Sheets("Velocity").Range("V2:V" & VelocityLastRow).Formula = "=CONCATENATE(A2,I2)" Sheets("Velocity").Range("V2:V" & VelocityLastRow).Value = Sheets("Velocity").Range("V2:V" & VelocityLastRow).Value LastRow = Sheets("Main Tab").Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row Sheets("Main Tab").Range("U2:U" & LastRow).Formula = "=CONCATENATE(E2,C2)" Sheets("Main Tab").Range("U2:U" & LastRow).Value = Sheets("Main Tab").Range("U2:U" & LastRow).Value Sheets("Main Tab").Range("T2:W" & LastRow).Value = Sheets("Main Tab").Range("T2:W" & LastRow).Value ActiveWorkbook.Worksheets("Main Tab").Sort.SortFields.Clear ActiveWorkbook.Worksheets("Main Tab").Sort.SortFields.Add Key:=Range( _ "U2:U" & LastRow), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:= _ xlSortNormal With ActiveWorkbook.Worksheets("Main Tab").Sort .SetRange Range("A1:AU" & LastRow) .Header = xlYes .MatchCase = False .Orientation = xlTopToBottom .Apply End With Sheets("Main Tab").Range("I2:I" & LastRow).Formula = "=IF(H2<>0,F2/H2)" Sheets("Main Tab").Range("J2:J" & LastRow).Formula = "=IF(VLOOKUP(CONCATENATE(U2,D2,CalculateWeek),Velocity!J$2:K$" & VelocityLastRow & ",1,TRUE)=CONCATENATE(U2,D2,CalculateWeek), VLOOKUP(CONCATENATE(U2,D2,CalculateWeek),Velocity!J$2:K$" & VelocityLastRow & ",2,TRUE), ""Missing"")" Sheets("Main Tab").Range("K2:K" & LastRow).Formula = "=IF(VLOOKUP(CONCATENATE(U2,D2,CalculateWeek),Velocity!J$2:N$" & VelocityLastRow & ",1,TRUE)=CONCATENATE(U2,D2,CalculateWeek), VLOOKUP(CONCATENATE(U2,D2,CalculateWeek),Velocity!J$2:N$" & VelocityLastRow & ",5,TRUE), ""Missing"")" Sheets("Main Tab").Range("L2:L" & LastRow).Formula = "=IF(AND(K2<>0,I2>K2),ROUND((F2/K2)/J2,0.1),"""")" Sheets("Main Tab").Range("M2:M" & LastRow).Formula = "=IF(AND(F2>0,L2<>""""),L2-H2,"""")" Sheets("Main Tab").Range("N2:N" & LastRow).Formula = "=IF(AND(L2>0,L2<>"""",M2<>""""),FLOOR(M2/IF(VLOOKUP(CONCATENATE(E2,C2,D2,CalculateWeek),Velocity!J$2:N$" & VelocityLastRow & ",1,TRUE)=CONCATENATE(E2,C2,D2,CalculateWeek), VLOOKUP(CONCATENATE(E2,C2,D2,CalculateWeek),Velocity!J$2:N$" & VelocityLastRow & ",3,TRUE), ""Missing""),1),"""")" Sheets("Main Tab").Range("O2:O" & LastRow).Formula = "=IF(AND(N2>IF(VLOOKUP(CONCATENATE(E2,C2,D2,CalculateWeek),Velocity!J$2:N$" & VelocityLastRow & ",1,TRUE)=CONCATENATE(E2,C2,D2,CalculateWeek), VLOOKUP(CONCATENATE(E2,C2,D2,CalculateWeek),Velocity!J$2:N$" & VelocityLastRow & ",4,TRUE), ""Missing""), N2<>""""),IF(VLOOKUP(CONCATENATE(E2,C2,D2,CalculateWeek),Velocity!J$2:N$" & VelocityLastRow & ",1,TRUE)=CONCATENATE(E2,C2,D2,CalculateWeek), VLOOKUP(CONCATENATE(E2,C2,D2,CalculateWeek),Velocity!J$2:N$" & VelocityLastRow & ",4,TRUE), ""Missing""),N2)" Sheets("Main Tab").Range("I2:O" & LastRow).Value = Sheets("Main Tab").Range("I2:O" & LastRow).Value Sheets("Main Tab").Range("Z2:Z" & LastRow).Formula = "=ROUND((N2*K2),0)" Sheets("Main Tab").Range("Z2:Z" & LastRow).Value = Sheets("Main Tab").Range("Z2:Z" & LastRow).Value ActiveWorkbook.Worksheets("Main Tab").Sort.SortFields.Clear ActiveWorkbook.Worksheets("Main Tab").Sort.SortFields.Add Key:=Range( _ "Z2:Z" & LastRow), SortOn:=xlSortOnValues, Order:=xlDescending, DataOption:= _ xlSortNormal With ActiveWorkbook.Worksheets("Main Tab").Sort .SetRange Range("A1:AU" & LastRow) .Header = xlYes .MatchCase = False .Orientation = xlTopToBottom .Apply End With Sheets("Main Tab").Range("X2:X" & LastRow).Formula = "=INDEX('Quantity Available'!$D$2:$D$" & LastRowAvail & ",MATCH(U2&""LIBERTY"",'Quantity Available'!$F$2:$F$" & LastRowAvail & ",FALSE))" Sheets("Main Tab").Range("X2:X" & LastRow).Value = Sheets("Main Tab").Range("X2:X" & LastRow).Value Dim i As Long Dim j As Long Dim myval1 As Long Dim myval2 As Long Dim myval3 As Long Dim MyRange1 As Range Dim MyRange2 As Range Dim MyRange3 As Range For i = 2 To LastRow Set MyRange1 = Worksheets("Main Tab").Range("U1:U" & i) Set MyRange2 = Worksheets("Main Tab").Range("Z1:Z" & i) Set MyRange3 = Worksheets("Main Tab").Range("X" & i) myval1 = Application.WorksheetFunction.SumIf(MyRange1, Range("U" & i).Value, MyRange2) myval2 = MyRange3 myval3 = myval2 - myval1 Worksheets("Main Tab").Cells(i, 18).Value = myval3 Next Sheets("Main Tab").Range("S2:S" & LastRow) = "LIBERTY" Sheets("Main Tab").Range("AC2:AC" & LastRow).Formula = "=IF(R2<0,""C"","""")" Sheets("Main Tab").Range("AC2:AC" & LastRow).Value = Sheets("Main Tab").Range("AC2:AC" & LastRow).Value Sheets("Main Tab").Range("AB2:AB" & LastRow).Formula = "=IF(AND(Z2>VLOOKUP(CONCATENATE(E2,C2,D2,CalculateWeek),Velocity!$J$2:$N$" & VelocityLastRow & ",4), Z2<>""""),VLOOKUP(CONCATENATE(E2,C2,D2,CalculateWeek),Velocity!$J$2:$N$" & VelocityLastRow & ",4),Z2)" Sheets("Main Tab").Range("AB2:AB" & LastRow).Value = Sheets("Main Tab").Range("AB2:AB" & LastRow).Value Sheets("Main Tab").Range("AA2:AA" & LastRow).Formula = "=IF(AC2=""C"",0,AB2)" Sheets("Main Tab").Range("AA2:AA" & LastRow).Value = Sheets("Main Tab").Range("AA2:AA" & LastRow).Value Dim myval4 As Long Dim MyRange4 As Range Dim MyRange5 As Range For j = 2 To 500 ' LastRow Set MyRange4 = Worksheets("Main Tab").Range("A2:A" & LastRow) Set MyRange5 = Worksheets("Main Tab").Range("AA2:AA" & LastRow) myval4 = Application.WorksheetFunction.SumIf(MyRange4, Range("A" & j).Value, MyRange5) Worksheets("Main Tab").Cells(j, 31).Value = myval4 Next LastRowCorrection = Null LastRowAvail = Null VelocityLastRow = Null LastRowData = Null LastRow = Null Application.ScreenUpdating = False Application.DisplayStatusBar = False Application.Calculation = xlCalculationManual Application.EnableEvents = False Application.CutCopyMode = False LastRow = Sheets("Main Tab").Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row Sheets("Main Tab").Range("AI2:AI" & LastRow).Formula = "=IF($E2="""","""",INDEX(Velocity!G:G,MATCH('Main Tab'!E2,Velocity!A:A,FALSE)))"
Sheets("Main Tab").Range("AR2:AR" & LastRow).Formula = "=IF($F2=0,0,ROUND((((($F2/$K2)/$J2)-$H2)/AI2),0.1))" Sheets("Main Tab").Range("AH2:AH" & LastRow).Formula = "=IF($K2=0,0,IF($F2=0,0,ROUND((((($F2/$K2)/$J2)-$H2)/AI2)*$K2,0.1)))"
Sheets("Main Tab").Range("AG2:AG" & LastRow).Formula = "=IF(AH2<0,0,AH2)"
Sheets("Main Tab").Range("AK2:AK" & LastRow).Formula = "=IF($A2="""","""",1+CalculateWeek)" Sheets("Main Tab").Range("AL2:AL" & LastRow).Formula = "=IF(AK2="""","""",CONCATENATE($E2,AK2))"
Sheets("Main Tab").Range("AM2:AM" & LastRow).Formula = "=INDEX(Velocity!C:C,MATCH('Main Tab'!AL2,Velocity!V:V,FALSE))"
Sheets("Main Tab").Range("AN2:AN" & LastRow).Formula = "=IF(K2=0,0,IF(AM2=0,0,ROUND((((($F2/$K2)*AM2)-$F2)-($H2-$F2))/AI2,0.1)))" Sheets("Main Tab").Range("AO2:AO" & LastRow).Formula = "=IF(AN2="""","""",IF(AN2<0,0,AN2))" Sheets("Main Tab").Range("AP2:AP" & LastRow).Formula = "=IF(AO2="""","""",AO2-AG2)" Sheets("Main Tab").Range("AS2:AS" & LastRow).Formula = "=IF(K2<$AR$1,0,IF($AR2<0,"""",$AR2))" Sheets("Main Tab").Range("AF2:AF" & LastRow).Formula = "=IF(K2>$AR$1,AS2,MAX(AG2,AO2))" Sheets("Main Tab").Range("AU2:AU" & LastRow).Formula = "=IF(AE2<Parameters!$B$7,0,'Main Tab'!AA2)" Sheets("Main Tab").Range("AT2:AT" & LastRow).Formula = "=CONCATENATE(A2,V2,AU2)" Sheets("Main Tab").Range("AA2:AU" & LastRow).Value = Sheets("Main Tab").Range("AA2:AU" & LastRow).Value Application.CutCopyMode = False LastRowCorrection = Null LastRow = Null MainTabLastColum = Null QuantityLastColumn = Null End Sub Sub GenDoc_Click() DisableOptimize LastRow = Sheets("Main Tab").Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row With ActiveWorkbook.Worksheets("Main Tab") .AutoFilterMode = False With .Range("A1:AU1") .AutoFilter .AutoFilter Field:=29, Criteria1:="<>C" .AutoFilter Field:=47, Criteria1:="<>0" End With End With Set rData = Sheets("Main Tab").Range("A2:A" & LastRow) 'change this to suit your needs Set rVis = rData.SpecialCells(xlCellTypeVisible) rVis.Copy Sheets("Order Upload").Select Sheets("Order Upload").Range("A2").Select ActiveSheet.Paste LastRowUpload = Sheets("Order Upload").Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row Set rData = Sheets("Main Tab").Range("B2:B" & LastRow) 'change this to suit your needs Set rVis = rData.SpecialCells(xlCellTypeVisible) rVis.Copy Sheets("Order Upload").Select Sheets("Order Upload").Range("J2").Select ActiveSheet.Paste Set rData = Sheets("Main Tab").Range("S2:S" & LastRow) 'change this to suit your needs Set rVis = rData.SpecialCells(xlCellTypeVisible) rVis.Copy Sheets("Order Upload").Select Sheets("Order Upload").Range("B2").Select ActiveSheet.Paste Set rData = Sheets("Main Tab").Range("E2:E" & LastRow) 'change this to suit your needs Set rVis = rData.SpecialCells(xlCellTypeVisible) rVis.Copy Sheets("Order Upload").Select Sheets("Order Upload").Range("E2").Select ActiveSheet.Paste Set rData = Sheets("Main Tab").Range("AU2:AU" & LastRow) 'change this to suit your needs Set rVis = rData.SpecialCells(xlCellTypeVisible) rVis.Copy Sheets("Order Upload").Select Sheets("Order Upload").Range("F2").Select ActiveSheet.Paste Sheets("AOS Info").Select LastRowAOS = Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row Sheets("Order Upload").Select LastRowOrder = Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row VelocityLastRow = Sheets("Velocity").Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row Range("C2:C" & LastRowOrder).Formula = "=ShpDt" Range("D2:D" & LastRowOrder).Formula = "=PONumber" Range("G2:G" & LastRowOrder).Formula = "=VLOOKUP(E2,'AOS Info'!$D$2:$E$" & LastRowAOS & ",2,FALSE)" Range("H2:H" & LastRowOrder).Formula = "=VLOOKUP(E2,'Velocity'!$A$2:$G$" & VelocityLastRow & ",7,FALSE)" Range("I2:I" & LastRowOrder).Formula = "=H2*F2" Sheets("Order Upload").Range("C2:D" & LastRowOrder).Value = Sheets("Order Upload").Range("C2:D" & LastRowOrder).Value Sheets("Order Upload").Range("G2:I" & LastRowOrder).Value = Sheets("Order Upload").Range("G2:I" & LastRowOrder).Value Sheets("Order Upload").Range("K2:K" & LastRowOrder).Formula = "=IF(B2="""",LEFT(J2,1),B2)" Sheets("Order Upload").Range("K2:K" & LastRowOrder).Value = Sheets("Order Upload").Range("K2:K" & LastRowOrder).Value ActiveWorkbook.Worksheets("Order Upload").Sort.SortFields.Clear ActiveWorkbook.Worksheets("Order Upload").Sort.SortFields.Add Key:=Range( _ "A2:A" & LastRowOrder), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:= _ xlSortNormal ActiveWorkbook.Worksheets("Order Upload").Sort.SortFields.Add Key:=Range( _ "B2:B" & LastRowOrder), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:= _ xlSortNormal With ActiveWorkbook.Worksheets("Order Upload").Sort .SetRange Range("A1:K" & LastRowOrder) .Header = xlYes .MatchCase = False .Orientation = xlTopToBottom .SortMethod = xlPinYin .Apply End With LastRowCorrection = Null LastRowTransfer = Null LastRowMain = Null 'SecondsElapsed = Round(Timer - StartTime, 2) 'MsgBox "This code ran successfully in " & SecondsElapsed & " seconds", vbInformation 'StartTime = 0 'Sheets("Main Tab").Select 'ActiveSheet.ShowAllData End Sub Sub DisableOptimize() 'Disable As Copying Reenable at end Application.ScreenUpdating = False Application.DisplayStatusBar = False Application.Calculation = xlCalculationManual Application.EnableEvents = False End Sub Sub EnableOptimize() 'Disable As Copying Reenable at end Application.ScreenUpdating = True Application.DisplayStatusBar = True Application.Calculation = xlCalculationAutomatic Application.EnableEvents = True Application.CutCopyMode = False End Sub Sub UnfilterAll() Dim ws As Worksheet On Error Resume Next For Each ws In ThisWorkbook.Worksheets If ws.Visible Then ws.ShowAllData Next ws On Error GoTo 0 End Sub Sub SkuDCSummary() Worksheets("SKU-DC SUmmary").Select DisableOptimize 'Dim StartTime As Double 'Dim SecondsElapsed As Double 'StartTime = Timer Worksheets("Main Tab").AutoFilterMode = False LastRowMain = Sheets("Main Tab").Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row Sheets("Main Tab").Range("E1:E" & LastRowMain).AdvancedFilter Action:=xlFilterCopy, CopyToRange:=Sheets("SKU-DC Summary").Range("B1"), Unique:=True Sheets("Main Tab").Range("U1:U" & LastRowMain).AdvancedFilter Action:=xlFilterCopy, CopyToRange:=Sheets("SKU-DC Summary").Range("O1"), Unique:=True LastRowSKU = Sheets("SKU-DC Summary").Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row LastRowAOS = Sheets("AOS Info").Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row Sheets("SKU-DC Summary").Range("A2:A" & LastRowSKU).Formula = "=INDEX('Main Tab'!$C$2:$C$" & LastRowMain & ",MATCH(B2,'Main Tab'!$E$2:$E$" & LastRowMain & ",FALSE))" Sheets("SKU-DC Summary").Range("C2:C" & LastRowSKU).Formula = "=INDEX('AOS Info'!$E$2:$E$" & LastRowAOS & ",MATCH(B2,'AOS Info'!$A$2:$A$" & LastRowAOS & ",FALSE))" Sheets("SKU-DC Summary").Range("D2:D" & LastRowSKU).Formula = "=INDEX('AOS Info'!$B$2:$B$" & LastRowAOS & ",MATCH(B2,'AOS Info'!$A$2:$A$" & LastRowAOS & ",FALSE))" Dim myval1 As Long Dim MyRange1 As Range Dim MyRange2 As Range 'Dim MyRange3 As Range 'Dim MyRange4 As Range For j = 2 To LastRowSKU Set MyRange1 = Worksheets("Main Tab").Range("U2:U" & LastRowMain) Set MyRange2 = Worksheets("Main Tab").Range("F2:F" & LastRowMain) 'Set MyRange3 = Worksheets("Main Tab").Range("E2:E" & LastRowMain) 'Set MyRange4 = Worksheets("Main Tab").Range("E2:E" & LastRowMain) myval1 = Application.WorksheetFunction.SumIf(MyRange1, Range("O" & j).Value, MyRange2) Worksheets("SKU-DC Summary").Cells(j, 5).Value = myval1 Next 'Sheets("SKU-DC Summary").Range("E2:E" & LastRowSKU).Formula = "=SUMIFS('Main Tab'!$F2:$F$" & LastRowMain & ",'Main Tab'!$C2:$C$" & LastRowMain & ",A2,'Main Tab'!$E2:$E$" & LastRowMain & ",B2)"

Sheets("SKU-DC Summary").Range("F2:F" & LastRowSKU).Formula = "=E2/COUNTIFS('Main Tab'!$E2:$E$" & LastRowMain & ",B2,'Main Tab'!$H2:$H$" & LastRowMain & ","">0"")"

Sheets("SKU-DC Summary").Range("A2:F" & LastRowSKU).Value = Sheets("SKU-DC Summary").Range("A2:F" & LastRowSKU).Value

Sheets("SKU-DC Summary").Range("G2:G" & LastRowSKU).Formula = "=COUNTIFS('Main Tab'!$C2:$C$" & LastRowMain & ",A2,'Main Tab'!$E2:$E$" & LastRowMain & ",B2,'Main Tab'!$H2:$H$" & LastRowMain & ","">0"")" For j = 2 To LastRowSKU Set MyRange1 = Worksheets("Main Tab").Range("E2:E" & LastRowMain) Set MyRange2 = Worksheets("Main Tab").Range("H2:H" & LastRowMain) 'Set MyRange3 = Worksheets("Main Tab").Range("E2:E" & LastRowMain) 'Set MyRange4 = Worksheets("Main Tab").Range("E2:E" & LastRowMain) myval1 = Application.WorksheetFunction.SumIf(MyRange1, Range("B" & j).Value, MyRange2) Worksheets("SKU-DC Summary").Cells(j, 8).Value = myval1 - Range("E" & j).Value Next 'Sheets("SKU-DC Summary").Range("H2:H" & LastRowSKU).Formula = "=SUMIF('Main Tab'!$E$2:$E$" & LastRowMain & ",B2,'Main Tab'!$H$2:$H$" & LastRowMain & ")-E2" 'Sheets("SKU-DC Summary").Range("G2:H" & LastRowSKU).Value = Sheets("SKU-DC Summary").Range("G2:H" & LastRowSKU).Value Sheets("SKU-DC Summary").Range("I2:I" & LastRowSKU).Formula = "=H2/G2" Sheets("SKU-DC Summary").Range("J2:J" & LastRowSKU).Formula = "=E2/(H2+E2)" Sheets("SKU-DC Summary").Range("I2:J" & LastRowSKU).Value = Sheets("SKU-DC Summary").Range("I2:J" & LastRowSKU).Value 'Sheets("SKU-DC Summary").Range("O2:O" & LastRowSKU).Formula = "=B2 & A2" Sheets("SKU-DC Summary").Range("O2:O" & LastRowSKU).Value = Sheets("SKU-DC Summary").Range("O2:O" & LastRowSKU).Value For j = 2 To LastRowSKU Set MyRange1 = Worksheets("Main Tab").Range("U2:U" & LastRowMain) Set MyRange2 = Worksheets("Main Tab").Range("AU2:AU" & LastRowMain) myval1 = Application.WorksheetFunction.SumIf(MyRange1, Range("O" & j).Value, MyRange2) Worksheets("SKU-DC Summary").Cells(j, 11).Value = myval1 Next For j = 2 To LastRowSKU Set MyRange1 = Worksheets("Main Tab").Range("U2:U" & LastRowMain) Set MyRange2 = Worksheets("Main Tab").Range("AU2:AU" & LastRowMain) myval1 = Application.WorksheetFunction.SumIf(MyRange1, Range("O" & j).Value, MyRange2) Worksheets("SKU-DC Summary").Cells(j, 13).Value = myval1 Next Sheets("SKU-DC Summary").Range("K2:O" & LastRowSKU).Value = Sheets("SKU-DC Summary").Range("K2:O" & LastRowSKU).Value EnableOptimize LastRowCorrection = Null LastRowTransfer = Null LastRowMain = Null 'SecondsElapsed = Round(Timer - StartTime, 2) 'MsgBox "This code ran successfully in " & SecondsElapsed & " seconds", vbInformation 'StartTime = 0 End Sub Sub RunAll() Dim StartTime As Double Dim SecondsElapsed As Double StartTime = Timer Call Clear_Click Call LoadMicroData2 Call Calculate_Click Call GenDoc_Click Call SkuDCSummary SecondsElapsed = Round(Timer - StartTime, 2) MsgBox "This code ran successfully in " & SecondsElapsed & " seconds", vbInformation StartTime = 0 End Sub  • Have you considered making changes and timing the results? Feb 24 '17 at 18:48 • That's a good idea. I've done that for MatLab code back in school, but didn't even think about that being a functionality of Excel. It takes 2 hours to run in full for 500 lines, but I'll try timing each section and running it independently. Feb 24 '17 at 18:51 • Excel 32-bit has explicit limitations on file size that 64-bit does not. Feb 24 '17 at 19:17 • Especially in the loops, pull the worksheet data from the cells into a memory array instead of working directly on the worksheet. That will give you a huge speed-up in your code. Feb 24 '17 at 20:57 • The current question title, which states your concerns about the code, applies to too many questions on this site to be useful. The site standard is for the title to simply state the task accomplished by the code. Please see How to Ask for examples, and revise the title accordingly. – Jamal Mar 4 '17 at 5:04 1 Answer Oh, please forgive me if my tone come across abrasive - I know you're just trying to maintain and learn, which is why I bring this all up! Don't feel bad, I'm self-taught too and if I ran into that thing, I could not have fixed it when I started learning. First things first, variables. Please use them. Right now the whole thing seems overwhelming because it is. dim orderSheet as Worksheet set orderSheet = Sheets("Order Upload") 'etc for the rest Sheets("Main Tab") Sheets("Microstrategy Data") Sheets("Velocity") Sheets("Quantity Available")  OR Worksheets have a CodeName property - View Properties window (F4) and the (Name) field (the one at the top) can be used as the worksheet name. This way you can avoid Sheets("mySheet") and instead just use mySheet and you won't even have to declare variables! Formulas, do you need to print them on the sheet? Sheets("Main Tab").Range("AC2:AC" & LastRow).Formula = "=IF(R2<0,""C"","""")"  Why not make the calculation and then print that to the sheet? Or do you need them? Dim MyRange4 As Range Dim MyRange5 As Range  Goodness, what are these? Ranges? Doing what? Variable names - give your variables meaningful names. For instance: For j = 2 To 500 ' LastRow Set MyRange4 = Worksheets("Main Tab").Range("A2:A" & LastRow) Set MyRange5 = Worksheets("Main Tab").Range("AA2:AA" & LastRow) myval4 = Application.WorksheetFunction.SumIf(MyRange4, Range("A" & j).Value, MyRange5) Worksheets("Main Tab").Cells(j, 31).Value = myval4 Next  I can't imagine what's going on here without going back through the entire module and figuring out what each thing is. Wouldn't it be easier to follow with something like: For index = 2 To lastrow Set quantities = MainTab.Range("quantities") Set prices = MainTab.Range("prices") cost = 1 'calculation CostSheet.Range("total") = cost Next  Or better yet, arrays. But one step at a time. Try refactoring all the hard-coded ranges and sheets into variables. Either CodeNames and Named Ranges or a range variable describing what the range is. Speed Formulas If I search the macro for the word "formula" I come up with 53 hits. That's 53 different times you've set a formula. And many of those formulas are for more than one cell in a range. Of course excel will hang when calculations are turned back on - imagine how many calculations that is. If you can use values instead of formulas, please do. If not - please tell us why. Loops I see seven loops For i = 2 to lastRow For j = 2 to lastRow For Each ws in thisworkbook.sheets For j = 2 to LastRowSKU For j = 2 to LastRowSKU For j = 2 to LastRowSKU For j = 2 to LastRowSKU  See those last 4? Or even all 6? Why are you iterating over that four separate times? Why not do everything in the single loop? You also have If lastRow > 1 Then  Five times.. in a row! Seems to me you could pull a function out of there for refactoring. Also, speaking of lastrow - There is a standard way to find lastRow and lastColumn. That post explains why. Example You pointed to Sheets("Main Tab").Range("O2:O" & LastRow).Formula = "=IF(AND(N2>IF(VLOOKUP(CONCATENATE(E2,C2,D2,CalculateWeek),Velocity!J$2:N$" & VelocityLastRow & ",1,TRUE)=CONCATENATE(E2,C2,D2,CalculateWeek), VLOOKUP(CONCATENATE(E2,C2,D2,CalculateWeek),Velocity!J$2:N$" & VelocityLastRow & ",4,TRUE), ""Missing""), N2<>""""),IF(VLOOKUP(CONCATENATE(E2,C2,D2,CalculateWeek),Velocity!J$2:N$" & VelocityLastRow & ",1,TRUE)=CONCATENATE(E2,C2,D2,CalculateWeek), VLOOKUP(CONCATENATE(E2,C2,D2,CalculateWeek),Velocity!J$2:N$" & VelocityLastRow & ",4,TRUE), ""Missing""),N2)"  as a single formula, right? Const LOOKUP_MATCH as String = “Missing” Dim lookupString as string lookupString = CONCATENATE(E2,C2,D2,CalculateWeek) dim velocityLookupRange as Range set velocityLookupRange = Velocity.Range(cells(2,10),cells(lastRow,13)) dim lookupCell as Range Set lookupCell = Range(“N2”) Dim returnColumn as Long ReturnColumn = 4  The formula would now be MainTab("O2:O" & LastRow).Formula = "=IF(AND(LOOKUPCELL>IF(VLOOKUP(LookupString),VelocityLookupRange,1,1)=LookupString), VLOOKUP(LookupString),VelocityLookupRange,returnColumn,1), LOOKUP_MATCH), LOOKUPCELL<>""""),IF(VLOOKUP(LookupString),VelocityLookupRange,1,1)=LookupString), VLOOKUP(LookupString),VelocityLookupRange,returnColumn,1), LOOKUP_MATCH),LOOKUPCELL)"  Still overwhelming! Let's get out Notepad++ to figure this thing out: Sheets("Main Tab").Range("O2:O" & LastRow).Formula = =IF( AND( N2>IF(VLOOKUP(CONCATENATE(E2,C2,D2,CalculateWeek),Velocity!J$2:N$" & VelocityLastRow & ",1,TRUE)=CONCATENATE(E2,C2,D2,CalculateWeek), VLOOKUP(CONCATENATE(E2,C2,D2,CalculateWeek),Velocity!J$2:N$" & VelocityLastRow & ",4,TRUE), ""Missing""), N2<>""""), THEN IF(VLOOKUP(CONCATENATE(E2,C2,D2,CalculateWeek),Velocity!J$2:N$" & VelocityLastRow & ",1,TRUE)=CONCATENATE(E2,C2,D2,CalculateWeek), VLOOKUP(CONCATENATE(E2,C2,D2,CalculateWeek),Velocity!J$2:N$" & VelocityLastRow & ",4,TRUE), ""Missing"") ELSE: ,N2)  I think boils down to (in VBA) IF NOT N2 = vbNullString Then if N2 > someResult then someResult end if Else: MISSING end if  With dim someResult as String someResult = VLOOKUP(CONCATENATE(E2,C2,D2,CalculateWeek),Velocity!J$2:N\$" & VelocityLastRow & ",1,TRUE) = lookupString


I'm still not sure I got that right. How can you debug this? Is the error because it's taking forever to calculate or because the formula string is too many characters? Or is there a missing result?

• Thank you very much! I'll probably have tons of questions, but for now I'll work my way through these improvements. Then when I ask a question I can post the new and improved (hopefully) code with annotations and defined variables! Feb 26 '17 at 17:48
• Definitely. Even if you just make a few improvements, you can always post again for more suggestions. There are a lot of people here that can offer different opinions and ideas! Feb 26 '17 at 17:48
• I think (and please correct me if I'm wrong) you messed up the quotes on the Example block. There are some literals being considered as variables and vice-versa. E.g. the formula in the "The formula would now be" part is all literals instead of consider the variables and the very last line doesn't seem right Feb 27 '17 at 20:34
• @VictorMoraes the formula is using VBA variables, just to try to illustrate the point. Feb 27 '17 at 20:42