Shopping Cart

No products in the cart.

Lex Chip and Sawdust Deliveries Excel Macro (1A)

Egger Excel Macros’s Docs ▸ Lex Chip and Sawdust Deliveries Excel Macro (1A)

This software uses a GPLv3 Software License, if you are unfamiliar with the terms of this license check the attachments to this file.

 

Sub Button2_Click()

 

‘You might be able to delete these variables (do so at your own risk)                                             
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 = Sheet1.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 = Sheet1.Range(“B1:ZZ1”).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

 

‘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

 

 

Attachments

Tags: Excel, Macro, VBA

Children: Lex Chip and Sawdust Deliveries Excel Macro (2A)

Report

Harassment or bullying behavior
Contains mature or sensitive content
Contains misleading or false information
Contains abusive or derogatory content
Contains spam, fake content or potential malware

Block Member?

Please confirm you want to block this member.

You will no longer be able to:

  • See blocked member's posts
  • Mention this member in posts
  • Invite this member to groups
  • Message this member
  • Add this member as a connection

Please note: This action will also remove this member from your connections and send a report to the site admin. Please allow a few minutes for this process to complete.

Report

You have already reported this