Let me start out by saying I am new to both this site and VBA (I took a crash course in high school, so I get basic coding jargon). I have been looking for several days now to find the code that would work to do what I need, but I haven't found anything.
Basically I have a bunch of sand samples that have been sieved. Every sample gets its own sheet. From here, I have a Master Summary Sheet that collects the data I need to plot from these other sheets and puts it into a table. It also finds the type of sample and the date of the test. There are 6 different types of sample (so far).
Also, I need the summary table to be plotted with date on the x-axis, and percent on the y-axis. I need each sample type to have its own series. I have gotten to where I have all 6 series divided correctly (though I am sure the code is awfully inefficient) but I can't figure out how to get the value from the column next to the sample type to be plotted. In other words, everything is stuck at a value of "0" because it is sorting it by string right now.
I've put my code and a text version of my excel sheet below. I appreciate any help you can give me!
'Sheet Date Type Sieve #40
'Truck 47533 4/15/2016 Truck 55%
'Truck 47272 4/4/2016 Truck 55%
'47272 4/4/2016 CoA 48%
'Basement 4-4 4/4/2016 Basement 55%
'Bin2 4-4 4/4/2016 Bin2 55%
'Bin1 4-4 4/4/2016 Bin1 55%
'Hopper 4-4 4/4/2016 Hopper 57%
'Basement 4-1 4/1/2016 Basement 58%
'Bin2 4-1 4/1/2016 Bin2 54%
'Bin1 4-1 4/1/2016 Bin1 58%
'Hopper 4-1 4/1/2016 Hopper 56%
'Truck 46892 4/1/2016 Truck 56%
'46892 4/1/2016 CoA 47%
'Basement 3-24 3/24/2016 Basement 55%
'Bin2 3-24 3/24/2016 Bin2 57%
'Bin1 3-24 3/24/2016 Bin1 61%
'Hopper 3-24 3/24/2016 Hopper 50%
Sub ChartingSub()
Dim LastRow As Long
Dim c As Range
Dim Rng1 As Range
Dim Truck As Range
Dim Hopper As Range
Dim Bin1 As Range
Dim Bin2 As Range
Dim Basement As Range
Dim coa As Range
Dim NewSand As Range
Dim ShName As String
Dim dates As Range
If ActiveSheet.ChartObjects.Count > 0 Then
ActiveSheet.ChartObjects.Delete
End If
With ActiveSheet
LastRow = .Range("D" & .Rows.Count).End(xlUp).Row
Set Rng1 = .Range("C2:C" & LastRow)
ShName = .Name
End With
With ActiveSheet
Set dates = .Range("B2:B" & LastRow)
End With
For Each c In Rng1
If c.Value = "Truck" Then
If Not Truck Is Nothing Then
Set Truck = Union(Truck, c)
Else
Set Truck = c
End If
ElseIf c.Value = "Hopper" Then
If Not Hopper Is Nothing Then
Set Hopper = Union(Hopper, c)
Else
Set Hopper = c
End If
ElseIf c.Value = "Bin1" Then
If Not Bin1 Is Nothing Then
Set Bin1 = Union(Bin1, c)
Else
Set Bin1 = c
End If
ElseIf c.Value = "Bin2" Then
If Not Bin2 Is Nothing Then
Set Bin2 = Union(Bin2, c)
Else
Set Bin2 = c
End If
ElseIf c.Value = "Basement" Then
If Not Basement Is Nothing Then
Set Basement = Union(Basement, c)
Else
Set Basement = c
End If
ElseIf c.Value = "CoA" Then
If Not coa Is Nothing Then
Set coa = Union(coa, c)
Else
Set coa = c
End If
ElseIf c.Value = "NewSand" Then
If Not NewSand Is Nothing Then
Set NewSand = Union(NewSand, c)
Else
Set NewSand = c
End If
End If
Next
Dim cht As Chart
Set cht = ActiveWorkbook.Charts.Add
Set cht = cht.Location(Where:=xlLocationAsObject, Name:=ShName)
With cht
.ChartType = xlXYScatterLines
.HasTitle = True
.ChartTitle.Text = "Sieve #40 Trend"
End With
Dim t As Series
Set t = cht.SeriesCollection.NewSeries
With t
.Values = Truck
.XValues = dates
.Name = "Truck"
End With
Dim h As Series
Set h = cht.SeriesCollection.NewSeries
With h
.Values = Hopper
.XValues = dates
.Name = "Hopper"
End With
Dim b As Series
Set b = cht.SeriesCollection.NewSeries
With b
.Values = Basement
.XValues = dates
.Name = "Basement Reclaim"
End With
Dim b1 As Series
Set b1 = cht.SeriesCollection.NewSeries
With b1
.Values = Bin1
.XValues = dates
.Name = "Bin1"
End With
Dim b2 As Series
Set b2 = cht.SeriesCollection.NewSeries
With b2
.Values = Bin2
.XValues = dates
.Name = "Bin2"
End With
Dim cert As Series
Set cert = cht.SeriesCollection.NewSeries
With cert
.Values = coa
.XValues = dates
.Name = "CoA"
End With
'Dim ns As Series
'Set ns = cht.SeriesCollection.NewSeries
'With ns
'.Values = NewSand
'.XValues = dates
'.Name = "New Resin Sand"
'End With
End Sub
If you need a plot where X is date, Y is percent, and "Z" is the type. Then you need to do the following:
In Excel you need to create each series individually - so if you have say 5 types then you need to plot each one separately.
The easiest way to do that is to sort by type, loop over all the rows, and figure out the boundaries so type 1 maybe rows 2-11, type 2 12-15, etc.
Then you can plot each series
Something like this - where the start and end is found using above:
For a = 1 To lastrow
With ActiveChart
With .SeriesCollection.NewSeries
.XValues = Sheets(strName).Range("E" & startx & ":E" & endx)
.Values = Sheets(strName).Range("E" & starty & ":E" & endy)
.Name = strName
End With
End If
End With
Next a