VBA Self populate page name in formula

Edwardvanschothorst

New Member
Joined
Sep 20, 2021
Messages
14
Office Version
  1. 365
Platform
  1. 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
 

Attachments

  • parts list.PNG
    parts list.PNG
    36.9 KB · Views: 11
  • 1d_2.PNG
    1d_2.PNG
    55.5 KB · Views: 11

johnnyL

Well-known Member
Joined
Nov 7, 2011
Messages
4,091
Office Version
  1. 2016
  2. 2013
  3. 2007
Platform
  1. Windows
Ok this takes me back to post #17 here.

You keep saying 'one line' on the sheet, what one line are you referring to?
You mentioned an error triggered, what error?
'Need to have data in any row below 22'? what does that mean?

Please post an example that does work.
 

Excel Facts

Fastest way to copy a worksheet?
Hold down the Ctrl key while dragging tab for Sheet1 to the right. Excel will make a copy of the worksheet.

Edwardvanschothorst

New Member
Joined
Sep 20, 2021
Messages
14
Office Version
  1. 365
Platform
  1. Windows
JohnnyL - Thank you for your time on this, and sorry for not being entirely clear on everything. As you can see I am a rookie programmer
I found the final solution I was looking for by adding the if statement to your line below. The second set of shown below is the final working set. Your help was very much appreciated!

VBA Code:
     If IsEmpty(Range("a23").Value) = False Then
                    Range("E22:F22").autofill Destination:=Range("E22:F" & LastPopulatedRow), Type:=xlFillDefault
                End If

VBA Code:
Sub autofill()
'
    ColumnIncrementer = -1
'
    For i = 1 To Worksheets.Count
        CurSheetName = Sheets(i).Name

'
        If Left(CurSheetName, 3) = "1D_" And IsNumeric(Mid(CurSheetName, 4, 1)) Then
            ColumnIncrementer = ColumnIncrementer + 1
        Sheets(CurSheetName).Select
            LastPopulatedRow = Range("A" & Rows.Count).End(xlUp).Row
'
            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)"
            
                If IsEmpty(Range("a23").Value) = False Then
                    Range("E22:F22").autofill Destination:=Range("E22:F" & LastPopulatedRow), Type:=xlFillDefault
                End If

            Sheets("Parts List").Select
'
            Cells(14, 5 + ColumnIncrementer) = "=SUMPRODUCT(('" & CurSheetName & "'!$B$22:$B$140='Parts List'!$B14)*'" & CurSheetName & "'!$F$22:$F$140)*'" & CurSheetName & "'!$B$2"
'
            LastPopulatedRow = Range("A" & Rows.Count).End(xlUp).Row
'
            Range(Cells(14, 5 + ColumnIncrementer), Cells(LastPopulatedRow, 5 + ColumnIncrementer)).FillDown
'
            MsgBox CurSheetName & " calculated"
        End If
    Next i
End Sub
 

johnnyL

Well-known Member
Joined
Nov 7, 2011
Messages
4,091
Office Version
  1. 2016
  2. 2013
  3. 2007
Platform
  1. Windows
Glad you finally solved it!
 

Forum statistics

Threads
1,186,064
Messages
5,955,633
Members
438,208
Latest member
stephicohu

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top