Edwardvanschothorst
New Member
 Joined
 Sep 20, 2021
 Messages
 14
 Office Version

 365
 Platform

 Windows
First post here  I have been reading this forum for years, but this time I could not find what I am looking for so I am looking for a little help.
I am working on a worksheet that calculates part costs after I run after I optimize the cut lengths. I am using the 1dcutx add in to optimize my linear stock. (1DCutX  Length Cutting Optimization for Excel)
Once the stock is optimized it adds a range of worksheets starting at 1d_1  1d_xx depending on the different amount of layouts required for the job. Each of these sheets needs to have a formula added to distribute the waste length over the number of parts cut out of each length of material, and calculate the part cost based on material length. (part costs comes from my stock worksheet) Once these formula's are added I need to get the totals together in my parts worksheet so I can find the average cost of each part if it comes from various lengths. I have the script worked out to calculate this, but I have to write this code for each page that might exist. Is there a way to set this formula up so that it looks at all pages instead of writing this for each possible page? I could not get the mini uploader to work so I included screenshots of the parts list page, and the code for 1d_2, and 1d_3 below.
Private Sub two()
For i = 1 To Worksheets.Count
If Worksheets(i).Name = "1D_2" Then
Sheets("1D_2").Select
Range("e22") = "=roundup((b$7+2)/count(c$22:c$200)+c22+.055,3)"
Range("F22") = "=(VLOOKUP(A22,STOCK!$A$3:$C$20,3,FALSE)/VLOOKUP(A22,STOCK!$A$3:$C$20,2,FALSE)*E22)"
LastPopulatedRow = Range("A" & Rows.Count).End(xlUp).Row
Range("e22: " & "f" & LastPopulatedRow).FillDown
Sheets("Parts List").Select
Range("f14") = "=SUMPRODUCT(('1D_2'!$B$22:$B$140='Parts List'!$B14)*'1D_2'!$F$22:$F$140)*'1D_2'!$B$2"
LastPopulatedRow = Range("A" & Rows.Count).End(xlUp).Row
Range("f14: " & "f" & LastPopulatedRow).FillDown
MsgBox "1d_2 calculated"
End If
Next i
End Sub
Private Sub three()
For i = 1 To Worksheets.Count
If Worksheets(i).Name = "1D_3" Then
Sheets("1D_3").Select
Range("e22") = "=roundup((b$7+2)/count(c$22:c$200)+c22+.055,3)"
Range("F22") = "=(VLOOKUP(A22,STOCK!$A$3:$C$20,3,FALSE)/VLOOKUP(A22,STOCK!$A$3:$C$20,2,FALSE)*E22)"
LastPopulatedRow = Range("A" & Rows.Count).End(xlUp).Row
Range("e22: " & "f" & LastPopulatedRow).FillDown
Sheets("Parts List").Select
Range("g14") = "=SUMPRODUCT(('1D_3'!$B$22:$B$140='Parts List'!$B14)*'1D_3'!$F$22:$F$140)*'1D_3'!$B$2"
LastPopulatedRow = Range("A" & Rows.Count).End(xlUp).Row
Range("g14: " & "g" & LastPopulatedRow).FillDown
MsgBox "1d_3 calculated"
End If
Next i
End Sub
I am working on a worksheet that calculates part costs after I run after I optimize the cut lengths. I am using the 1dcutx add in to optimize my linear stock. (1DCutX  Length Cutting Optimization for Excel)
Once the stock is optimized it adds a range of worksheets starting at 1d_1  1d_xx depending on the different amount of layouts required for the job. Each of these sheets needs to have a formula added to distribute the waste length over the number of parts cut out of each length of material, and calculate the part cost based on material length. (part costs comes from my stock worksheet) Once these formula's are added I need to get the totals together in my parts worksheet so I can find the average cost of each part if it comes from various lengths. I have the script worked out to calculate this, but I have to write this code for each page that might exist. Is there a way to set this formula up so that it looks at all pages instead of writing this for each possible page? I could not get the mini uploader to work so I included screenshots of the parts list page, and the code for 1d_2, and 1d_3 below.
Private Sub two()
For i = 1 To Worksheets.Count
If Worksheets(i).Name = "1D_2" Then
Sheets("1D_2").Select
Range("e22") = "=roundup((b$7+2)/count(c$22:c$200)+c22+.055,3)"
Range("F22") = "=(VLOOKUP(A22,STOCK!$A$3:$C$20,3,FALSE)/VLOOKUP(A22,STOCK!$A$3:$C$20,2,FALSE)*E22)"
LastPopulatedRow = Range("A" & Rows.Count).End(xlUp).Row
Range("e22: " & "f" & LastPopulatedRow).FillDown
Sheets("Parts List").Select
Range("f14") = "=SUMPRODUCT(('1D_2'!$B$22:$B$140='Parts List'!$B14)*'1D_2'!$F$22:$F$140)*'1D_2'!$B$2"
LastPopulatedRow = Range("A" & Rows.Count).End(xlUp).Row
Range("f14: " & "f" & LastPopulatedRow).FillDown
MsgBox "1d_2 calculated"
End If
Next i
End Sub
Private Sub three()
For i = 1 To Worksheets.Count
If Worksheets(i).Name = "1D_3" Then
Sheets("1D_3").Select
Range("e22") = "=roundup((b$7+2)/count(c$22:c$200)+c22+.055,3)"
Range("F22") = "=(VLOOKUP(A22,STOCK!$A$3:$C$20,3,FALSE)/VLOOKUP(A22,STOCK!$A$3:$C$20,2,FALSE)*E22)"
LastPopulatedRow = Range("A" & Rows.Count).End(xlUp).Row
Range("e22: " & "f" & LastPopulatedRow).FillDown
Sheets("Parts List").Select
Range("g14") = "=SUMPRODUCT(('1D_3'!$B$22:$B$140='Parts List'!$B14)*'1D_3'!$F$22:$F$140)*'1D_3'!$B$2"
LastPopulatedRow = Range("A" & Rows.Count).End(xlUp).Row
Range("g14: " & "g" & LastPopulatedRow).FillDown
MsgBox "1d_3 calculated"
End If
Next i
End Sub