Can anyone tell me what line needs to but changed or put into this code so that my last chart with two series will read from 0 up. Some of my data in the range for the series are negative numbers and it makes the chart look ridiculous and I really don't need the negative data.
Sub CreateCharts()
Dim chts() As Variant
Dim cObj As Shape
Dim cht As Chart
Dim chtLeft As Double, chtTop As Double, chtWidth As Double, chtHeight As Double
Dim lastRow As Long
Dim c As Long
Dim ws As Worksheet
Set ws = ActiveSheet
lastRow = ws.Range("A1", Range("A2").End(xlDown)).Rows.Count
c = -1
'## Create an array of chart names in this sheet. ##'
For Each cObj In ActiveSheet.Shapes
If cObj.HasChart Then
ReDim Preserve chts(c)
chts(c) = cObj.Name
c = c + 1
End If
Next
'## Check to see if your charts exist on the worksheet ##'
If c = -1 Then
ReDim Preserve chts(0)
chts(0) = ""
End If
If IsError(Application.Match("RPM", chts, False)) Then
'## Add this chart ##'
chtLeft = ws.Cells(lastRow, 1).Left
chtTop = ws.Cells(lastRow + 2, 1).Top + ws.Cells(lastRow + 2, 1).Height
Set cObj = ws.Shapes.AddChart(xlLine, chtLeft, chtTop, 355, 211)
cObj.Name = "RPM"
cObj.Chart.HasTitle = True
Set cht = cObj.Chart
cht.ChartTitle.Characters.Text = "RPM"
clearChart cht
End If
If IsError(Application.Match("Pressure/psi", chts, False)) Then
'## Add this chart ##'
With ws.ChartObjects("RPM")
chtLeft = .Left + .Width + 10
chtTop = .Top
Set cObj = ws.Shapes.AddChart(xlLine, chtLeft, chtTop, 355, 211)
cObj.Name = "Pressure/psi"
cObj.Chart.HasTitle = True
Set cht = cObj.Chart
cht.ChartTitle.Characters.Text = "Pressure/psi"
clearChart cht
End With
End If
If IsError(Application.Match("Third Chart", chts, False)) Then
'## Add this chart ##'
With ws.ChartObjects("Pressure/psi")
chtLeft = .Left + .Width + 10
chtTop = .Top
Set cObj = ws.Shapes.AddChart(xlLine, chtLeft, chtTop, 355, 211)
cObj.Name = "Third Chart"
cObj.Chart.HasTitle = True
Set cht = cObj.Chart
cht.ChartTitle.Characters.Text = "Third Chart"
clearChart cht
End With
End If
End Sub
Sub clearChart(cht As Chart)
Dim srs As Series
For Each srs In cht.SeriesCollection
If Not cht.SeriesCollection.Count = 1 Then srs.Delete
Next
End Sub
Sub UpdateCharts()
Dim cObj As ChartObject
Dim cht As Chart
Dim shtName As String
Dim chtName As String
Dim xValRange As Range
Dim lastRow As Long
With ActiveSheet
lastRow = .Range("B" & .Rows.Count).End(xlUp).Row
Set xValRange = .Range("B2:B" & lastRow)
shtName = .Name & " "
End With
'## This sets values for Series 1 in each chart ##'
For Each cObj In ActiveSheet.ChartObjects
Set cht = cObj.Chart
chtName = shtName & cht.Name
If cht.SeriesCollection.Count = 0 Then
'## Add a dummy series which will be replaced in the code below ##'
With cht.SeriesCollection.NewSeries
.Values = "{1,2,3}"
.XValues = xValRange
End With
End If
'## Assuming only one series per chart, we just reset the Values & XValues per chart ##'
With cht.SeriesCollection(1)
'## Assign the category/XValues ##'
.XValues = xValRange
'## Here, we set the range to use for Values, based on the chart name: ##'
Select Case Replace(chtName, shtName, vbNullString)
Case "RPM"
.Values = xValRange.Offset(0, 3) '## Column E is 3 offset from the xValRange in column B
.Name = "RPM"
Case "Pressure/psi"
.Values = xValRange.Offset(0, 5) '## Column G is 5 offset from the xValRange in column B
.Name = "Pressure/psi"
Case "Third Chart"
.Values = xValRange.Offset(0, 6) '## Column H is 6 offset from the xValRange in column B
.Name = "Demand burn off"
'## Make sure this chart has 2 series, if not, add a dummy series ##'
If cht.SeriesCollection.Count < 2 Then
With cht.SeriesCollection.NewSeries
.XValues = "{1,2,3}"
End With
End If
'## add the data for second series: ##'
cht.SeriesCollection(2).XValues = xValRange
cht.SeriesCollection(2).Values = xValRange.Offset(0, 8) '## Column J is 8 offset from the xValRange in column B
cht.SeriesCollection(2).Name = "Step Burn Off"
Case "Add as many of these Cases as you need"
End Select
End With
Next
End Sub
Chart2.jpg
Bookmarks