| You are currently viewing a revision titled "Lex Chip and Sawdust Deliveries Excel Macro (2A)", saved on June 22, 2022 at 11:06 am by Patrick Burcham | |
|---|---|
| Title | Lex Chip and Sawdust Deliveries Excel Macro (2A) |
| Content | This software uses a GPLv3 Software License, if you are unfamiliar with the terms of this license check the attachments to this file.
Sub Lex_Chips_And_Sawdust()
Dim myValueRowE As String
Dim myValueRowF As String
Dim myValueCol As String
'Generate the Input Box to enter the Starting Week of the multiple charts at the bottom of the screen
myValueColStart = InputBox("Insert the week and year (ww.yyyy) you want the Data Series to start with (If you enter a week between 1 and 9 enter a zero before the week number here)")
'Generate the Input Box to enter the Ending Week of the multiple charts at the bottom of the screen
myValueColEnd = InputBox("Insert the week and year (ww.yyyy) you want the Data Series to end with (If you enter a week between 1 and 9 enter a zero before the week number here)")
'Get the value of the date entered in the box and return the column value
Set Cell = Worksheets("Delivery STN by Week").Cells.Find(myValueColStart, , xlValues, xlPart, , , False)
If Not Cell Is Nothing Then
ColLetterStart = Split(Cell.Address, "$")(1)
Else
MsgBox "I cannot find that text on this sheet"
End If
Set Cell = Worksheets("Delivery STN by Week").Cells.Find(myValueColEnd, , xlValues, xlPart, , , False)
If Not Cell Is Nothing Then
ColLetterEnd = Split(Cell.Address, "$")(1)
Else
MsgBox "I cannot find that text on this sheet"
End If
'Generate the row of the Matched Vendor number for chips
Dim myMatchedRowE As String
Set FoundCellRowE = Sheet2.Range("C4:C72").Find(what:=126302)
myMatchedRowE = FoundCellRowE.Row
Dim myMatchedRowJordanChips As String
Set FoundMatchedJordanChips = Sheet2.Range("C4:C72").Find(what:=122405)
myMatchedRowJordanChips = FoundMatchedJordanChips.Row
Dim myMatchedRowHighCountryChips As String
Set FoundRowHighCountryChips = Sheet2.Range("C4:C72").Find(what:=121427)
myMatchedRowHighCountryChips = FoundRowHighCountryChips.Row
Dim myMatchedRowIndependenceLumberChips As String
Set FoundRowIndependenceLumberChips = Sheet2.Range("C4:C72").Find(what:=134101)
myMatchedRowIndependenceLumberChips = FoundRowIndependenceLumberChips.Row
Dim myMatchedRowCulpChips As String
Set FoundRowCulpChips = Sheet2.Range("C4:C72").Find(what:=122406)
myMatchedRowCulpChips = FoundRowCulpChips.Row
Dim myMatchedRowMcDowell As String
Set FoundRowMcDowell = Sheet2.Range("C4:C72").Find(what:=122491)
myMatchedRowMcDowell = FoundRowMcDowell.Row
Dim myMatchedRowPineProductChips As String
Set FoundRowPineProductChips = Sheet2.Range("C4:C72").Find(what:=131853)
myMatchedRowPineProductChips = FoundRowPineProductChips.Row
Dim myMatchedRowHopkinsChips As String
Set FoundRowHopkinsChips = Sheet2.Range("C4:C72").Find(what:=131860)
myMatchedRowHopkinsChips = FoundRowHopkinsChips.Row
Dim myMatchedRowCanfor As String
Set FoundRowCanfor = Sheet2.Range("C2:C72").Find(what:=121423)
myMatchedRowCanfor = FoundRowCanfor.Row
Dim myMatchedRowHopkinsSawdust As String
Set FoundRowHopkinsSawdust = Sheet2.Range("C74:C127").Find(what:=131860)
myMatchedRowHopkinsSawdust = FoundRowHopkinsSawdust.Row
Dim myMatchedRowJordanSawdust As String
Set FoundRowJordanSawdust = Sheet2.Range("C74:C127").Find(what:=122405)
myMatchedRowJordanSawdust = FoundRowJordanSawdust.Row
Dim myMatchedRowCulpSawdust As String
Set FoundRowCulpSawdust = Sheet2.Range("C74:C127").Find(what:=122406)
myMatchedRowCulpSawdust = FoundRowCulpSawdust.Row
Dim myMatchedRowMcDowellSawdust As String
Set FoundRowMcDowellSawdust = Sheet2.Range("C74:C127").Find(what:=122491)
myMatchedRowMcDowellSawdust = FoundRowMcDowellSawdust.Row
Dim myMatchedRowPiedmontSawdust As String
Set FoundRowPiedmontSawdust = Sheet2.Range("C74:C127").Find(what:=132671)
myMatchedRowPiedmontSawdust = FoundRowPiedmontSawdust.Row
'Pull all the Values/Ranges from their respective rows
myValueRowE = "'Delivery STN by week'" + "!$" + ColLetterStart + "$" + myMatchedRowE + ":$" + ColLetterEnd + "$" + myMatchedRowE
myValueRowJordanChips = "'Delivery STN by week'" + "!$" + ColLetterStart + "$" + myMatchedRowJordanChips + ":$" + ColLetterEnd + "$" + myMatchedRowJordanChips
myValueRowHighCountryChips = "'Delivery STN by week'" + "!$" + ColLetterStart + "$" + myMatchedRowHighCountryChips + ":$" + ColLetterEnd + "$" + myMatchedRowHighCountryChips
myValueRowIndependenceLumberChips = "'Delivery STN by week'" + "!$" + ColLetterStart + "$" + myMatchedRowIndependenceLumberChips + ":$" + ColLetterEnd + "$" + myMatchedRowIndependenceLumberChips
myValueRowCulpChips = "'Delivery STN by week'" + "!$" + ColLetterStart + "$" + myMatchedRowCulpChips + ":$" + ColLetterEnd + "$" + myMatchedRowCulpChips
myValueRowMcDowell = "'Delivery STN by week'" + "!$" + ColLetterStart + "$" + myMatchedRowMcDowell + ":$" + ColLetterEnd + "$" + myMatchedRowMcDowell
myValueRowPineProductChips = "'Delivery STN by week'" + "!$" + ColLetterStart + "$" + myMatchedRowPineProductChips + ":$" + ColLetterEnd + "$" + myMatchedRowPineProductChips
myValueRowHopkinsChips = "'Delivery STN by week'" + "!$" + ColLetterStart + "$" + myMatchedRowHopkinsChips + ":$" + ColLetterEnd + "$" + myMatchedRowHopkinsChips
myValueRowCanfor = "'Delivery STN by week'" + "!$" + ColLetterStart + "$" + myMatchedRowCanfor + ":$" + ColLetterEnd + "$" + myMatchedRowCanfor
myValueRowHopkinsSawdust = "'Delivery STN by week'" + "!$" + ColLetterStart + "$" + myMatchedRowHopkinsSawdust + ":$" + ColLetterEnd + "$" + myMatchedRowHopkinsSawdust
myValueRowJordanSawdust = "'Delivery STN by week'" + "!$" + ColLetterStart + "$" + myMatchedRowJordanSawdust + ":$" + ColLetterEnd + "$" + myMatchedRowJordanSawdust
myValueRowCulpSawdust = "'Delivery STN by week'" + "!$" + ColLetterStart + "$" + myMatchedRowCulpSawdust + ":$" + ColLetterEnd + "$" + myMatchedRowCulpSawdust
myValueRowMcDowellSawdust = "'Delivery STN by week'" + "!$" + ColLetterStart + "$" + myMatchedRowMcDowellSawdust + ":$" + ColLetterEnd + "$" + myMatchedRowMcDowellSawdust
myValueRowPiedmontSawdust = "'Delivery STN by week'" + "!$" + ColLetterStart + "$" + myMatchedRowPiedmontSawdust + ":$" + ColLetterEnd + "$" + myMatchedRowPiedmontSawdust
'This gets the X-Values for all the ranges/series generated in the previous steps
myValueRowXAxis = "'Delivery STN by week'" + "!$" + ColLetterStart + "$1:$" + ColLetterEnd + "$1"
'Generate the Charts based on the values entered from previous steps
ActiveSheet.ChartObjects("Chart 2").Activate
ActiveChart.SeriesCollection(1).Select
ActiveChart.SetSourceData Source:=Sheet2.Range(myValueRowE)
ActiveChart.SeriesCollection(1).XValues = myValueRowXAxis
ActiveSheet.ChartObjects("Chart 3").Activate
ActiveChart.SeriesCollection(1).Select
ActiveChart.SetSourceData Source:=Sheet2.Range(myValueRowJordanChips)
ActiveChart.SeriesCollection(1).XValues = myValueRowXAxis
ActiveSheet.ChartObjects("Chart 4").Activate
ActiveChart.SeriesCollection(1).Select
ActiveChart.SetSourceData Source:=Sheet2.Range(myValueRowHighCountryChips)
ActiveChart.SeriesCollection(1).XValues = myValueRowXAxis
ActiveSheet.ChartObjects("Chart 5").Activate
ActiveChart.SeriesCollection(1).Select
ActiveChart.SetSourceData Source:=Sheet2.Range(myValueRowIndependenceLumberChips)
ActiveChart.SeriesCollection(1).XValues = myValueRowXAxis
ActiveSheet.ChartObjects("Chart 6").Activate
ActiveChart.SeriesCollection(1).Select
ActiveChart.SetSourceData Source:=Sheet2.Range(myValueRowMcDowell)
ActiveChart.SeriesCollection(1).XValues = myValueRowXAxis
ActiveSheet.ChartObjects("Chart 7").Activate
ActiveChart.SeriesCollection(1).Select
ActiveChart.SetSourceData Source:=Sheet2.Range(myValueRowCulpChips)
ActiveChart.SeriesCollection(1).XValues = myValueRowXAxis
ActiveSheet.ChartObjects("Chart 8").Activate
ActiveChart.SeriesCollection(1).Select
ActiveChart.SetSourceData Source:=Sheet2.Range(myValueRowPineProductChips)
ActiveChart.SeriesCollection(1).XValues = myValueRowXAxis
ActiveSheet.ChartObjects("Chart 9").Activate
ActiveChart.SeriesCollection(1).Select
ActiveChart.SetSourceData Source:=Sheet2.Range(myValueRowHopkinsChips)
ActiveChart.SeriesCollection(1).XValues = myValueRowXAxis
ActiveSheet.ChartObjects("Chart 10").Activate
ActiveChart.SeriesCollection(1).Select
ActiveChart.SetSourceData Source:=Sheet2.Range(myValueRowCanfor)
ActiveChart.SeriesCollection(1).XValues = myValueRowXAxis
ActiveSheet.ChartObjects("Chart 11").Activate
ActiveChart.SeriesCollection(1).Select
ActiveChart.SetSourceData Source:=Sheet2.Range(myValueRowHopkinsSawdust)
ActiveChart.SeriesCollection(1).XValues = myValueRowXAxis
ActiveSheet.ChartObjects("Chart 12").Activate
ActiveChart.SeriesCollection(1).Select
ActiveChart.SetSourceData Source:=Sheet2.Range(myValueRowJordanSawdust)
ActiveChart.SeriesCollection(1).XValues = myValueRowXAxis
ActiveSheet.ChartObjects("Chart 13").Activate
ActiveChart.SeriesCollection(1).Select
ActiveChart.SetSourceData Source:=Sheet2.Range(myValueRowCulpSawdust)
ActiveChart.SeriesCollection(1).XValues = myValueRowXAxis
ActiveSheet.ChartObjects("Chart 14").Activate
ActiveChart.SeriesCollection(1).Select
ActiveChart.SetSourceData Source:=Sheet2.Range(myValueRowMcDowellSawdust)
ActiveChart.SeriesCollection(1).XValues = myValueRowXAxis
ActiveSheet.ChartObjects("Chart 15").Activate
ActiveChart.SeriesCollection(1).Select
ActiveChart.SetSourceData Source:=Sheet2.Range(myValueRowPiedmontSawdust)
ActiveChart.SeriesCollection(1).XValues = myValueRowXAxis
End Sub |
| Excerpt | |
| Footnotes |