I first want to apologize for dumping a lot of code into here, but I have been stuck on this issue for days. I was assigned to build a planning screen wherein I could pull corresponding information regarding Sales, Production, and Inventory. As a preface to all of this, this code works for me and does exactly what I need it to do. I originally posted this in Stack Overflow, but someone mentioned I should also post it here. I also want to note that the SpeedUp and SpeedDown functions are in a different module and are used to affect: Screen Updating, Events, Calculations, and the Status Bar.
The problem, however, is the amount of time it takes to run. Originally, it was taking about 5-7 minutes, but I have been able to reduce it to 1-2 minutes depending on the computer being used. I have tried changing multiple things, and can not reduce the time more. Any input or advice would be greatly appreciated.
The problem, however, is the amount of time it takes to run. Originally, it was taking about 5-7 minutes, but I have been able to reduce it to 1-2 minutes depending on the computer being used. I have tried changing multiple things, and can not reduce the time more. Any input or advice would be greatly appreciated.
Code:
Sub FillInventoryAcross()
'This code is the formulas for the Total Inventory, Sales, and Production Data. Just addition formulas.
Call SpeedUp
Dim strFormulas(1 To 3) As Variant
With ThisWorkbook.Sheets("Inventory")
strFormulas(1) = "=SUM(C15,C20,C25,C30,C35,C40)"
strFormulas(2) = "=SUM(C16,C21,C26,C31,C36,C41)"
strFormulas(3) = "=SUM(C18,C23,C28,C33,C38,C43)"
.Range("C11:W11").Formula = strFormulas(1)
.Range("C12:W12").Formula = strFormulas(2)
.Range("C13:W13").Formula = strFormulas(3)
End With
End Sub
Sub FillInventoryPerLocation()
'This code will fill in the inventory per location. It will add up all of the sales, movement and production per plant along with the prior days inventory.
Call SpeedUp
Dim strformula(1 To 12) As Variant
With ThisWorkbook.Sheets("Inventory")
strformula(1) = "=IFERROR(IF(TRIM(Inventori!$D:$D)=""L95"",INDEX(Inventori!$E:$E,MATCH(Inventory!$M$3,Inventori!$B:$B,0))+SUM(C16:C19)+C46,0),0)"
strformula(2) = "=IFERROR(IF(TRIM(Inventori!$D:$D)=""L90"",INDEX(Inventori!$E:$E,MATCH(Inventory!$M$3,Inventori!$B:$B,0)),0)+SUM(C21:C24),0)"
strformula(3) = "=IFERROR(IF(TRIM(Inventori!$D:$D)=""L91"",INDEX(Inventori!$E:$E,MATCH(Inventory!$M$3,Inventori!$B:$B,0)),0)+SUM(C26:C29),0)"
strformula(4) = "=IFERROR(IF(TRIM(Inventori!$D:$D)=""L93"",INDEX(Inventori!$E:$E,MATCH(Inventory!$M$3,Inventori!$B:$B,0)),0)+SUM(C31:C34),0)"
strformula(5) = "=IFERROR(IF(TRIM(Inventori!$D:$D)=""L94"",INDEX(Inventori!$E:$E,MATCH(Inventory!$M$3,Inventori!$B:$B,0)),0)+SUM(C36:C39),0)"
strformula(6) = "=IFERROR(IF(TRIM(Inventori!$D:$D)=""A78"",INDEX(Inventori!$E:$E,MATCH(Inventory!$M$3,Inventori!$B:$B,0)),0)+SUM(C41:C44),0)"
strformula(7) = "=C15+Sum(D16:D19)+D46"
strformula(8) = "=C20+sum(D21:D24)"
strformula(9) = "=C25+sum(D26:D29)"
strformula(10) = "=C30+sum(D31:D34)"
strformula(11) = "=C35+sum(D36:D39)"
strformula(12) = "=C40+sum(D41:D44)"
.Range("C15").Formula = strformula(1)
.Range("C20").Formula = strformula(2)
.Range("C25").Formula = strformula(3)
.Range("C30").Formula = strformula(4)
.Range("C35").Formula = strformula(5)
.Range("C40").Formula = strformula(6)
.Range("D15:W15").Formula = strformula(7)
.Range("D20:W20").Formula = strformula(8)
.Range("D25:W25").Formula = strformula(9)
.Range("D30:W30").Formula = strformula(10)
.Range("D35:W35").Formula = strformula(11)
.Range("D40:W40").Formula = strformula(12)
End With
End Sub
Sub SumIfSales()
'This code will pull up all of the sales information for a product. Just a Sumif looking up information that matches Date/SKU. After the code is in the starting cell, it is then dragged accross for all of the other dates.
Call SpeedUp
Dim strformula(1 To 6) As Variant
With ThisWorkbook.Sheets("Inventory")
strformula(1) = "=(SUMIFS(Sales!$I$1:$I$200000,Sales!$B$1:$B$200000,""L95"",Sales!$D$1:$D$200000,CONCATENATE(Inventory!C$8,Inventory!$M$3),Sales!$AD$1:$AD$200000,""VN"")*-1)"
strformula(2) = "=(SUMIFS(Sales!$I$1:$I$200000,Sales!$B$1:$B$200000,""L90"",Sales!$D$1:$D$200000,CONCATENATE(Inventory!C$8,Inventory!$M$3),Sales!$AD$1:$AD$200000,""VN"")*-1)"
strformula(3) = "=(SUMIFS(Sales!$I$1:$I$200000,Sales!$B$1:$B$200000,""L91"",Sales!$D$1:$D$200000,CONCATENATE(Inventory!C$8,Inventory!$M$3),Sales!$AD$1:$AD$200000,""VN"")*-1)"
strformula(4) = "=(SUMIFS(Sales!$I$1:$I$200000,Sales!$B$1:$B$200000,""L93"",Sales!$D$1:$D$200000,CONCATENATE(Inventory!C$8,Inventory!$M$3),Sales!$AD$1:$AD$200000,""VN"")*-1)"
strformula(5) = "=(SUMIFS(Sales!$I$1:$I$200000,Sales!$B$1:$B$200000,""L94"",Sales!$D$1:$D$200000,CONCATENATE(Inventory!C$8,Inventory!$M$3),Sales!$AD$1:$AD$200000,""VN"")*-1)"
strformula(6) = "=(SUMIFS(Sales!$I$1:$I$200000,Sales!$B$1:$B$200000,""A78"",Sales!$D$1:$D$200000,CONCATENATE(Inventory!C$8,Inventory!$M$3),Sales!$AD$1:$AD$200000,""VN"")*-1)"
.Range("C16:W16").Formula = strformula(1)
.Range("C21:W21").Formula = strformula(2)
.Range("C26:W26").Formula = strformula(3)
.Range("C31:W31").Formula = strformula(4)
.Range("C36:W36").Formula = strformula(5)
.Range("C41:W41").Formula = strformula(6)
End With
End Sub
Sub SumIfMovement()
'This code works in a similar way to the prior code, but looks to match Date/SKU to find product movement.
Call SpeedUp
Dim strformula(1 To 6) As Variant
With ThisWorkbook.Sheets("Inventory")
strformula(1) = "=(SUMIFS(Sales!$I$1:$I$200000,Sales!$D$1:$D$200000,CONCATENATE(Inventory!C8,Inventory!$M$3),Sales!$AD$1:$AD$200000,""VT"",Sales!$O$1:$O$200000,""95"")-SUMIFS(Sales!$I$1:$I$200000,Sales!$D$1:$D$200000,CONCATENATE(Inventory!C8,Inventory!$M$3),Sales!$AD$1:$AD$200000,""VT"",Sales!$B$1:$B$200000,""L95""))"
strformula(2) = "=(SUMIFS(Sales!$I$1:$I$200000,Sales!$D$1:$D$200000,CONCATENATE(Inventory!C8,Inventory!$M$3),Sales!$AD$1:$AD$200000,""VT"",Sales!$O$1:$O$200000,""90"")-SUMIFS(Sales!$I$1:$I$200000,Sales!$D$1:$D$200000,CONCATENATE(Inventory!C8,Inventory!$M$3),Sales!$AD$1:$AD$200000,""VT"",Sales!$B$1:$B$200000,""L90""))"
strformula(3) = "=(SUMIFS(Sales!$I$1:$I$200000,Sales!$D$1:$D$200000,CONCATENATE(Inventory!C8,Inventory!$M$3),Sales!$AD$1:$AD$200000,""VT"",Sales!$O$1:$O$200000,""91"")-SUMIFS(Sales!$I$1:$I$200000,Sales!$D$1:$D$200000,CONCATENATE(Inventory!C8,Inventory!$M$3),Sales!$AD$1:$AD$200000,""VT"",Sales!$B$1:$B$200000,""L91""))"
strformula(4) = "=(SUMIFS(Sales!$I$1:$I$200000,Sales!$D$1:$D$200000,CONCATENATE(Inventory!C8,Inventory!$M$3),Sales!$AD$1:$AD$200000,""VT"",Sales!$O$1:$O$200000,""93"")-SUMIFS(Sales!$I$1:$I$200000,Sales!$D$1:$D$200000,CONCATENATE(Inventory!C8,Inventory!$M$3),Sales!$AD$1:$AD$200000,""VT"",Sales!$B$1:$B$200000,""L93""))"
strformula(5) = "=(SUMIFS(Sales!$I$1:$I$200000,Sales!$D$1:$D$200000,CONCATENATE(Inventory!C8,Inventory!$M$3),Sales!$AD$1:$AD$200000,""VT"",Sales!$O$1:$O$200000,""94"")-SUMIFS(Sales!$I$1:$I$200000,Sales!$D$1:$D$200000,CONCATENATE(Inventory!C8,Inventory!$M$3),Sales!$AD$1:$AD$200000,""VT"",Sales!$B$1:$B$200000,""L94""))"
strformula(6) = "=(SUMIFS(Sales!$I$1:$I$200000,Sales!$D$1:$D$200000,CONCATENATE(Inventory!C8,Inventory!$M$3),Sales!$AD$1:$AD$200000,""VT"",Sales!$O$1:$O$200000,""78"")-SUMIFS(Sales!$I$1:$I$200000,Sales!$D$1:$D$200000,CONCATENATE(Inventory!C8,Inventory!$M$3),Sales!$AD$1:$AD$200000,""VT"",Sales!$B$1:$B$200000,""A78""))"
.Range("C17:W17").Formula = strformula(1)
.Range("C22:W22").Formula = strformula(2)
.Range("C27:W27").Formula = strformula(3)
.Range("C32:W32").Formula = strformula(4)
.Range("C37:W37").Formula = strformula(5)
.Range("C42:W42").Formula = strformula(6)
End With
End Sub
Sub SumIfProduction()
'This code yet again works like the other two codes, but for production.
Call SpeedUp
Dim strformula(1 To 6) As Variant
With ThisWorkbook.Sheets("Inventory")
strformula(1) = "=(SUMIFS(Production!$Q$1:$Q$100000,Production!$B$1:$B$100000,CONCATENATE(Inventory!C$8,Inventory!$M$3),Production!$I$1:$ I$100000,""P95""))"
strformula(2) = "=(SUMIFS(Production!$Q$1:$Q$100000,Production!$B$1:$B$100000,CONCATENATE(Inventory!C$8,Inventory!$M$3),Production!$I$1:$ I$100000,""P90""))"
strformula(3) = "=(SUMIFS(Production!$Q$1:$Q$100000,Production!$B$1:$B$100000,CONCATENATE(Inventory!C$8,Inventory!$M$3),Production!$I$1:$ I$100000,""P91""))"
strformula(4) = "=(SUMIFS(Production!$Q$1:$Q$100000,Production!$B$1:$B$100000,CONCATENATE(Inventory!C$8,Inventory!$M$3),Production!$I$1:$ I$100000,""P93""))"
strformula(5) = "=(SUMIFS(Production!$Q$1:$Q$100000,Production!$B$1:$B$100000,CONCATENATE(Inventory!C$8,Inventory!$M$3),Production!$I$1:$ I$100000,""P94""))"
strformula(6) = "=(SUMIFS(Production!$Q$1:$Q$100000,Production!$B$1:$B$100000,CONCATENATE(Inventory!C$8,Inventory!$M$3),Production!$I$1:$ I$100000,""A78""))"
.Range("C18:W18").Formula = strformula(1)
.Range("C23:W23").Formula = strformula(2)
.Range("C28:W28").Formula = strformula(3)
.Range("C33:W33").Formula = strformula(4)
.Range("C38:W38").Formula = strformula(5)
.Range("C43:W43").Formula = strformula(6)
End With
End Sub
Sub DailySalesHistory()
'This code works to look up the Sales History by day for a given product. Takes each starting Monday and will add a day to it accross until Sunday, then the next week starts. Does the Date/SKU thing like the other sections. It then multiplies the end value by -1 to make the sales values positive, as the user would like to see them as.
Call SpeedUp
Dim strformula(1 To 7) As Variant
With ThisWorkbook.Sheets("Inventory")
strformula(1) = "=(SUMIFS(Sales!$I$1:$I$200000,Sales!$D$1:$D$200000,CONCATENATE(Inventory!$B51,Inventory!$M$3),Sales!$AD$1:$AD$200000,""VN""))"
strformula(2) = "=(SUMIFS(Sales!$I$1:$I$200000,Sales!$D$1:$D$200000,CONCATENATE(Inventory!$B51+1,Inventory!$M$3),Sales!$AD$1:$AD$200000,""VN""))"
strformula(3) = "=(SUMIFS(Sales!$I$1:$I$200000,Sales!$D$1:$D$200000,CONCATENATE(Inventory!$B51+2,Inventory!$M$3),Sales!$AD$1:$AD$200000,""VN""))"
strformula(4) = "=(SUMIFS(Sales!$I$1:$I$200000,Sales!$D$1:$D$200000,CONCATENATE(Inventory!$B51+3,Inventory!$M$3),Sales!$AD$1:$AD$200000,""VN""))"
strformula(5) = "=(SUMIFS(Sales!$I$1:$I$200000,Sales!$D$1:$D$200000,CONCATENATE(Inventory!$B51+4,Inventory!$M$3),Sales!$AD$1:$AD$200000,""VN""))"
strformula(6) = "=(SUMIFS(Sales!$I$1:$I$200000,Sales!$D$1:$D$200000,CONCATENATE(Inventory!$B51+5,Inventory!$M$3),Sales!$AD$1:$AD$200000,""VN""))"
strformula(7) = "=(SUMIFS(Sales!$I$1:$I$200000,Sales!$D$1:$D$200000,CONCATENATE(Inventory!$B51+6,Inventory!$M$3),Sales!$AD$1:$AD$200000,""VN""))"
.Range("D51:D108").Formula = strformula(1)
.Range("E51:E108").Formula = strformula(2)
.Range("F51:F108").Formula = strformula(3)
.Range("G51:G108").Formula = strformula(4)
.Range("H51:H108").Formula = strformula(5)
.Range("I51:I108").Formula = strformula(6)
.Range("J51:J108").Formula = strformula(7)
End With
End Sub
Sub WeeklySalesHistory()
'This code will take all of the valuse returned in the prior code and add them together. This will give the user the total sales of a product for a given week.
Call SpeedUp
Worksheets("Inventory").Range("K51:K108").Formula = "=SUM(D51:J51)"
End Sub
Sub TopDaysoftheWeek()
'This code will bring up the days of the week for three weeks. Starts with Sunday and ends with Saturday. First formula finds the Sunday, the other formulas just adds 1 to the day.
Call SpeedUp
Worksheets("Inventory").Range("C8").FormulaArray = "=TODAY()-WEEKDAY(TODAY(),2)"
Worksheets("Inventory").Range("D8:W8").Formula = "=C8+1"
End Sub
Sub InventoryInfo()
'This code runs vlookups on the inputted SKU number to pull up corresponding information. If the cell is blank, it will tell the user what will come up. If there is an error, it will reflect that.
Call SpeedUp
Dim strformula(1 To 23) As Variant
With ThisWorkbook.Sheets("Inventory")
strformula(1) = "=IF(ISBLANK($M$3),""SKU Number"",INDEX(ItemMaster!$B:$B,MATCH(Inventory!$M$3,ItemMaster!$B:$B,0)))"
strformula(2) = "=IF(ISBLANK($M$3),""Product Name"",IF(ISTEXT(INDEX(ItemMaster!$D:$D,MATCH(Inventory!$M$3,ItemMaster!$B:$B,0))),INDEX(ItemMaster!$D:$D,MATCH(Inventory!$M$3 ,ItemMaster!$B:$B,0)),INDEX(ItemMaster!$C:$C,MATCH(Inventory!$M$3,ItemMaster!$B:$B,0))))"
strformula(3) = "=IF(ISBLANK($M$3),""Pieces Per Case in"",INDEX(ItemMaster!$I:$I,MATCH(Inventory!$M$3,ItemMaster!$B:$B,0)))&"" pieces"""
strformula(4) = "=IF(ISBLANK($M$3),""Pieces Per Case in "",(ROUND(INDEX(ItemMaster!$J:$J,MATCH(Inventory!$M$3,ItemMaster!$B:$B,0))*35.274,2)))&"" Oz"""
strformula(5) = "=IF(ISBLANK($M$3),""Date of Last Run"",MAX(IF(Production!$H:$H=Inventory!$M$3,Production!$N:$N)))"
strformula(6) = "=IF(ISBLANK($M$3),""Line of Last Run"",INDEX(Production!$E:$E,MATCH(Inventory!$M$3,Production!$H:$H,0)))"
strformula(7) = "=IF(ISBLANK($M$3),""Allergen Codes"",""Codes: ""&$AI$5)"
strformula(8) = "=IF(ISBLANK($M$3),"""",$AI$6)"
strformula(9) = "=IF(ISBLANK($M$3),"""",$AI$7)"
strformula(10) = "=IF(ISBLANK($M$3),"""",$AI$8)"
strformula(11) = "=IF(ISBLANK($M$3),"""",$AI$9)"
strformula(12) = "=IF(ISBLANK($M$3),"""",$AI$10)"
strformula(13) = "=IF(ISBLANK($M$3),"""",$AI$11)"
strformula(14) = "=IF(ISBLANK($M$3),"""",$AI$12)"
strformula(15) = "=IF(ISBLANK($M$3),""Cases Per Dough"",IF(ISNA(VLOOKUP(NUMBERVALUE($M$3),CPD!$A$1:$D$381,2,FALSE)),0,VLOOKUP(NUMBERVALUE($M$3),CPD!$A$1:$D$381,2,FALSE)))"
strformula(16) = "=IF(ISBLANK($M$3),""Lines Product Was Run On"",""Lines: ""&$AO$5)"
strformula(17) = "=IF(ISBLANK($M$3),"""",IF(OR(ISERR(AO6),ISNA(AO6)),"""",AO6))"
strformula(18) = "=IF(ISBLANK($M$3),"""",IF(OR(ISERR(AO7),ISNA(AO7)),"""",AO7))"
strformula(19) = "=IF(ISBLANK($M$3),"""",IF(OR(ISERR(AO8),ISNA(AO8)),"""",AO8))"
strformula(20) = "=IF(ISBLANK($M$3),"""",IF(OR(ISERR(AO9),ISNA(AO9)),"""",AO9))"
strformula(21) = "=IF(ISBLANK($M$3),""Average Cases Sold Per Week"", SUM(K56:K67)/12)"
strformula(22) = "=IF(ISBLANK($M$3),""Average Cases Sold Per Day"", $O$10/7)"
strformula(23) = "=IF(ISBLANK($M$3),""Days of Inventory Remaining"",(INDEX(Inventori!$F:$F,MATCH(Inventory!$M$3,Inventori!$B:$B,0)))/R10)"
.Range("B6").Formula = strformula(1)
.Range("C6").Formula = strformula(2)
.Range("F6").Formula = strformula(3)
.Range("I6").Formula = strformula(4)
.Range("L6").FormulaArray = strformula(5)
.Range("N6").Formula = strformula(6)
.Range("P6").Formula = strformula(7)
.Range("Q6").Formula = strformula(8)
.Range("R6").Formula = strformula(9)
.Range("S6").Formula = strformula(10)
.Range("T6").Formula = strformula(11)
.Range("U6").Formula = strformula(12)
.Range("V6").Formula = strformula(13)
.Range("W6").Formula = strformula(14)
.Range("E10").Formula = strformula(15)
.Range("J10").Formula = strformula(16)
.Range("K10").Formula = strformula(17)
.Range("L10").Formula = strformula(18)
.Range("M10").Formula = strformula(19)
.Range("N10").Formula = strformula(20)
.Range("O10").Formula = strformula(21)
.Range("R10").Formula = strformula(22)
.Range("U10").Formula = strformula(23)
End With
End Sub
Sub HiddenFormulas()
'This code runs some of the hidden formulas used to calculate and find factors for the inventory screen. The user will not be allowed to see or interact with them.
Call SpeedUp
Worksheets("Inventory").Range("AF5").Formula = "=INDEX(ItemMaster!$K:$K,MATCH($M$3,ItemMaster!$B:$B,0))"
Worksheets("Inventory").Range("AF6").Formula = "=INDEX(ItemMaster!$L:$L,MATCH($M$3,ItemMaster!$B:$B,0))"
Worksheets("Inventory").Range("AF7").Formula = "=INDEX(ItemMaster!$M:$M,MATCH($M$3,ItemMaster!$B:$B,0))"
Worksheets("Inventory").Range("AF8").Formula = "=INDEX(ItemMaster!$N:$N,MATCH($M$3,ItemMaster!$B:$B,0))"
Worksheets("Inventory").Range("AF9").Formula = "=INDEX(ItemMaster!$O:$O,MATCH($M$3,ItemMaster!$B:$B,0))"
Worksheets("Inventory").Range("AF10").Formula = "=INDEX(ItemMaster!$P:$P,MATCH($M$3,ItemMaster!$B:$B,0))"
Worksheets("Inventory").Range("AF11").Formula = "=INDEX(ItemMaster!$Q:$Q,MATCH($M$3,ItemMaster!$B:$B,0))"
Worksheets("Inventory").Range("AF12").Formula = "=INDEX(ItemMaster!$R:$R,MATCH($M$3,ItemMaster!$B:$B,0))"
Worksheets("Inventory").Range("AI5").Formula = "=IF($AF$5=""X"",""O"","""")"
Worksheets("Inventory").Range("AI6").Formula = "=IF(AF6=""X"",""A"","""")"
Worksheets("Inventory").Range("AI7").Formula = "=IF(AF7=""X"",""B"","""")"
Worksheets("Inventory").Range("AI8").Formula = "=IF(AF8=""X"",""C"","""")"
Worksheets("Inventory").Range("AI9").Formula = "=IF(AF9=""X"",""AB"","""")"
Worksheets("Inventory").Range("AI10").Formula = "=IF(AF10=""X"",""AC"","""")"
Worksheets("Inventory").Range("AI11").Formula = "=IF(AF11=""X"",""BC"","""")"
Worksheets("Inventory").Range("AI12").Formula = "=IF(AF12=""X"",""ABC"","""")"
Worksheets("Inventory").Range("AL5").FormulaArray = "=INDEX(Production!$E:$E, SMALL(IF(Inventory!$M$3=Production!$H:$H, ROW(Production!$H:$H)-ROW($A$1)+1), ROW(1:1)))"
Range("AL5:AL554").FillDown
Worksheets("Inventory").Range("AO5").FormulaArray = "=INDEX($AL$5:$AL$554, MATCH(0, COUNTIF($AO$4:AO4,$AL$5:$AL$554), 0))"
Worksheets("Inventory").Range("AO6").FormulaArray = "=INDEX($AL$5:$AL$554, MATCH(0, COUNTIF($AO$4:AO5,$AL$5:$AL$554), 0))"
Worksheets("Inventory").Range("AO7").FormulaArray = "=INDEX($AL$5:$AL$554, MATCH(0, COUNTIF($AO$4:AO6,$AL$5:$AL$554), 0))"
Worksheets("Invnetory").Range("AO8").FormulaArray = "=INDEX($AL$5:$AL$554, MATCH(0, COUNTIF($AO$4:AO7,$AL$5:$AL$554), 0))"
Worksheets("Inventory").Range("AO9").FormulaArray = "=INDEX($AL$5:$AL$554, MATCH(0, COUNTIF($AO$4:AO8,$AL$5:$AL$554), 0))"
End Sub
Sub BottomDaysoftheWeek()
'This code runs with TopDaysoftheWeek. Thile that code will pull up the date of each day, this code will convert that into the name of the day.
Call SpeedUp
Worksheets("Inventory").Range("C9:W9").Formula = "=TEXT(C8,""ddd"")"
End Sub
Sub SalesHistoryByMonth()
'This code finds the months for Sales History. It will find the month ahead of the current month all the way until a year prior.
Call SpeedUp
Dim strformula(1 To 14) As Variant
With ThisWorkbook.Sheets("Inventory")
strformula(1) = "=EOMONTH(TODAY(),0)+1"
strformula(2) = "=EOMONTH(TODAY(),-1)+1"
strformula(3) = "=EOMONTH(TODAY(),-2)+1"
strformula(4) = "=EOMONTH(TODAY(),-3)+1"
strformula(5) = "=EOMONTH(TODAY(),-4)+1"
strformula(6) = "=EOMONTH(TODAY(),-5)+1"
strformula(7) = "=EOMONTH(TODAY(),-6)+1"
strformula(8) = "=EOMONTH(TODAY(),-7)+1"
strformula(9) = "=EOMONTH(TODAY(),-8)+1"
strformula(10) = "=EOMONTH(TODAY(),-9)+1"
strformula(11) = "=EOMONTH(TODAY(),-10)+1"
strformula(12) = "=EOMONTH(TODAY(),-11)+1"
strformula(13) = "=EOMONTH(TODAY(),-12)+1"
strformula(14) = "=EOMONTH(TODAY(),-13)+1"
.Range("L51").Formula = strformula(1)
.Range("L53").Formula = strformula(2)
.Range("L55").Formula = strformula(3)
.Range("L57").Formula = strformula(4)
.Range("L59").Formula = strformula(5)
.Range("L61").Formula = strformula(6)
.Range("L63").Formula = strformula(7)
.Range("L65").Formula = strformula(8)
.Range("L67").Formula = strformula(9)
.Range("L69").Formula = strformula(10)
.Range("L71").Formula = strformula(11)
.Range("L73").Formula = strformula(12)
.Range("L75").Formula = strformula(13)
.Range("L77").Formula = strformula(14)
End With
End Sub
Sub SalesHistoryWeeks()
'This code finds the weeks of sales history. The first formula takes the first date of the inventory section and adds 6 weeks to it. The following formula just decreases the week by a week until the end of the table.
Call SpeedUp
Worksheets("Inventory").Range("B51").Formula = "=(C8+42)-WEEKDAY(C8,3)"
Worksheets("Inventory").Range("B52:B108").Formula = "=B51-7"
End Sub
Sub SalesHistoryMonthlyCalculations()
'This code calculates the sales history by month. It works by adding together all of the weekly sales history to the left of these formulas based on if the weeks are within the corresponding month. This formula is a little iffy, where it works on a beginning of the week basis (i.e. 5/28-6/4 counts as May, not May and June). Aside form that, works really well. May need to rework this formula.
Call SpeedUp
Dim strformula(1 To 14) As Variant
With ThisWorkbook.Sheets("Inventory")
strformula(1) = "=SUMIFS(K51:K108,B51:B108,"">=""&L51,B51:B108,""<=""&EOMONTH(L51,0))"
strformula(2) = "=SUMIFS(K51:K108,B51:B108,"">=""&L53,B51:B108,""<=""&EOMONTH(L53,0))"
strformula(3) = "=SUMIFS(K51:K108,B51:B108,"">=""&L55,B51:B108,""<=""&EOMONTH(L55,0))"
strformula(4) = "=SUMIFS(K51:K108,B51:B108,"">=""&L57,B51:B108,""<=""&EOMONTH(L57,0))"
strformula(5) = "=SUMIFS(K51:K108,B51:B108,"">=""&L59,B51:B108,""<=""&EOMONTH(L59,0))"
strformula(6) = "=SUMIFS(K51:K108,B51:B108,"">=""&L61,B51:B108,""<=""&EOMONTH(L61,0))"
strformula(7) = "=SUMIFS(K51:K108,B51:B108,"">=""&L63,B51:B108,""<=""&EOMONTH(L63,0))"
strformula(8) = "=SUMIFS(K51:K108,B51:B108,"">=""&L65,B51:B108,""<=""&EOMONTH(L65,0))"
strformula(9) = "=SUMIFS(K51:K108,B51:B108,"">=""&L67,B51:B108,""<=""&EOMONTH(L67,0))"
strformula(10) = "=SUMIFS(K51:K108,B51:B108,"">=""&L69,B51:B108,""<=""&EOMONTH(L69,0))"
strformula(11) = "=SUMIFS(K51:K108,B51:B108,"">=""&L71,B51:B108,""<=""&EOMONTH(L71,0))"
strformula(12) = "=SUMIFS(K51:K108,B51:B108,"">=""&L73,B51:B108,""<=""&EOMONTH(L73,0))"
strformula(13) = "=SUMIFS(K51:K108,B51:B108,"">=""&L75,B51:B108,""<=""&EOMONTH(L75,0))"
strformula(14) = "=SUMIFS(K51:K108,B51:B108,"">=""&L77,B51:B108,""<=""&EOMONTH(L77,0))"
.Range("L52").Formula = strformula(1)
.Range("L54").Formula = strformula(2)
.Range("L56").Formula = strformula(3)
.Range("L58").Formula = strformula(4)
.Range("L60").Formula = strformula(5)
.Range("L62").Formula = strformula(6)
.Range("L64").Formula = strformula(7)
.Range("L66").Formula = strformula(8)
.Range("L68").Formula = strformula(9)
.Range("L70").Formula = strformula(10)
.Range("L72").Formula = strformula(11)
.Range("L74").Formula = strformula(12)
.Range("L76").Formula = strformula(13)
.Range("L78").Formula = strformula(14)
End With
End Sub
Sub ProductionHistoryInfo()
'This code is all of the formulas regarding Production History. Finds Cases, Doughs, and Line product was run on. Also has the yield formulas (cases/doughs).
Call SpeedUp
Worksheets("Inventory").Range("Q52:Q104").Formula = "=IF(OR(($O52)="""",$O52=DATE(1900,1,0)),"""",INDEX(Production!$R:$R,MATCH(CONCATENATE(Inventory!$O52,Inventory!$M$3),Production!$B:$B,0)))"
Worksheets("Inventory").Range("R52:R104").Formula = "0"
Worksheets("Inventory").Range("S52:S104").Formula = "=IF(OR(($O52)="""",$O52=DATE(1900,1,0)),"""",INDEX(Production!$E:$E,MATCH(CONCATENATE(Inventory!$O52,Inventory!$M$3),Production!$B:$B,0)))"
Worksheets("Inventory").Range("T52:T104").Formula = "=IF(ISERR(Q52/R52),"""",Q52/R52)"
End Sub
Sub ProductionHistoryDates()
'This code finds the dates that a given product was run on. Uses an array function to look up dates of production based on matching SKU numbers. Most volatile function in this section. Can only lookup/match up to 20,000 values, which could be problematic. May need some editing done based on how actual tables are set up. Goes from oldest to newest, which may also be a problem.
Call SpeedUp
Worksheets("Inventory").Range("O52").FormulaArray = "=IFERROR(OFFSET(Production!$H$1:$H$100000,SMALL(IF(Production!$H$1:$H$100000=Inventory!$M$3,ROW(Production!$H$1:$H$10000 0)-ROW(INDEX(Production!$H$1:$H$100000,1,1))),ROW()-51),COLUMN()-9),"""")"
Range("O52:O104").FillDown
End Sub
Sub BorderFixer()
'This code was created after I discovered that some of these vba formulas will break the borders I made after running. Its only purpose is to fill in those broken borders and fix them to look like how they looked before.
With Worksheets("Inventory").Range("W10").Borders(xlEdgeBottom)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = 2
End With
With Worksheets("Inventory").Range("W7:W43").Borders(xlEdgeRight)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = 2
End With
With Worksheets("Inventory").Range("B108:K108").Borders(xlEdgeBottom)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = 2
End With
With Worksheets("Inventory").Range("K108").Borders(xlEdgeRight)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = 2
End With
End Sub
Sub ResetInventory()
'This code is made to run only if the user manages to unlock all of the cells on this sheet and start deleting the formulas I added. Once clicked, this code will: deactivate all of Excel's functions, run all of the prior codes related to inventory, run the border fixer to repair broken borders, and reactivate Excel's functions. While it runs fast and effectively, has a chance to break the worksheet. If so, click fix frozen cells. Will return error if the user tries to run this while sheets are protected.
Call SpeedUp
Call InventoryInfo
Call TopDaysoftheWeek
Call BottomDaysoftheWeek
Call SumIfSales
Call SumIfMovement
Call SumIfProduction
Call FillInventoryPerLocation
Call FillInventoryAcross
Call BorderFixer
Call CPD
Call SpeedDown
End Sub
Sub ResetSalesHistory()
'This code is made to run only if the user manages to unlock all of the cells on this sheet and start deleting the formulas I added. Once clicked, this code will: deactivate all of Excel's functions, run all of the prior codes related to sales history, run the border fixer to repair broken borders, and reactivate Excel's functions. While it runs fast and effectively, has a chance to break the worksheet. If so, click fix frozen cells. Will return error if the user tries to run this while sheets are protected.
Call SpeedUp
Call SalesHistoryWeeks
Call DailySalesHistory
Call WeeklySalesHistory
Call SalesHistoryByMonth
Call SalesHistoryMonthlyCalculations
Call BorderFixer
Call SpeedDown
End Sub
Sub ResetProductionHistory()
'This code is made to run only if the user manages to unlock all of the cells on this sheet and start deleting the formulas I added. Once clicked, this code will: deactivate all of Excel's functions, run all of the prior codes related to production history, run the border fixer to repair broken borders, and reactivate Excel's functions. While it runs fast and effectively, has a chance to break the worksheet. If so, click fix frozen cells. Will return error if the user tries to run this while sheets are protected.
Call SpeedUp
Call ProductionHistoryDates
Call ProductionHistoryInfo
Call BorderFixer
Call SpeedDown
End Sub
Sub InventoryPrintPreview()
'This code is made to show the users a print preview of what the worksheet will look like. I already made the print areas for the three tables on this sheet. User can change margins or format.
Application.ExecuteExcel4Macro "SHOW.TOOLBAR(""Ribbon"",True)"
Application.DisplayFormulaBar = True
ActiveWindow.DisplayWorkbookTabs = True
Worksheets("Inventory").PrintPreview
Application.ExecuteExcel4Macro "SHOW.TOOLBAR(""Ribbon"",False)"
Application.DisplayFormulaBar = False
ActiveWindow.DisplayWorkbookTabs = False
End Sub
Last edited by a moderator:
