[RESOLVED] Another Chart Problem

[pgag45]

Hey All,

I have a function which creates charts from a specified range (always the "A" column of months, and one of many other columns). I allow only part of the entire column to be graphed (any part from 1-30 or 10-25), or the entire thing if the user so desires. This is all working great, except that the x-axis is not labeled correctly. For example if I only wanted to graph from months 4-10, the chart does correctly display the data for these months, except that the x-axis is labeled from 1-7 instead of 4-10. Anything I can do to force the graph to label the axis with this format instead of just always starting at 1? Here's my code if that is of help. Thanks much

If chkVolume.Value = True Then
    Dim chartVolume As Chart
    rangeName = "a" & startMonth & ":a" & endMonth & ", " 'Reset
    rangeName = rangeName & "j" & startMonth & ":j" & endMonth
    Call AddChartSheet("NNH3 Volume", rangeName, "LBS / AC N", graphStyle, chartVolume)
End If
End Sub


Sub AddChartSheet(chartName As String, setRange As String, yTitle As String, graphStyle As Integer, chartOf As Chart)
   Set chartOf = Charts.Add
   For Each shtNext In Sheets
       If shtNext.Name = chartName Then 'Search/Delete charts w/ same name
            Application.DisplayAlerts = False 'No delete prompt
            Sheets(chartName).Delete
            Application.DisplayAlerts = True
       End If
   Next shtNext
   With chartOf
     .Name = chartName
      If graphStyle = 2 Then
        .ChartType = xlColumnClustered
      ElseIf graphStyle = 1 Then
        .ChartType = xlLine
      End If
      .SetSourceData Source:=Sheets("SOILSYM_MONTH").Range(setRange), _
        PlotBy:=xlColumns
      .HasTitle = True
      .ChartTitle.Text = chartName
      .Axes(xlCategory, xlPrimary).HasTitle = True
      .Axes(xlCategory, xlPrimary).AxisTitle.Characters.Text = "Month"
      .Axes(xlValue, xlPrimary).HasTitle = True
      .Axes(xlValue, xlPrimary).AxisTitle.Characters.Text = yTitle
      .SeriesCollection(1).Delete
      .HasLegend = False
   End With
End Sub

[Siddharth Rout]

.SeriesCollection(1).Delete

Why are you deleting the series collection?

Also if I want the chart to pick up values for X axis from A1:A5 then I would do something like...

.SeriesCollection(1).XValues = "=Sheet1!R1C1:R5C1"

If the data needs to be picked up from A4:A10 the I would use something like...

.SeriesCollection(1).XValues = "=Sheet1!R4C1:R10C1"

and so on...

ps: Replace "Sheet1" with the relevant sheet name...

Hope this helps...

[pgag45]

I delete Series1 because it just graphs the months in respect to the ... months... Its series 2 I am concerned about so I only display that.

Also with your method of seriescollection, can I use seriescollection(1).Yvalues and choose what I want as my y-axis?

thanks for the help kool

[pgag45]

and what is the difference b/w setsourcedata and seriescollection?

[Siddharth Rout]

Also with your method of seriescollection, can I use seriescollection(1).Yvalues and choose what I want as my y-axis?

To choose values for Y-Axis use this

'Change the range as appropriate...
'The range right now is B2:B9
 
.SeriesCollection(1).Values = "=Sheet1!R2C2:R9C2"

what is the difference b/w setsourcedata and seriescollection?

SetSourceData sets the source data range for the chart.

SeriesCollection returns an object that represents either a single series (a Series object) or a collection of all the series (a SeriesCollection collection) in the chart or chart group.

Using SeriesCllection gives you more control over the way you want to display your data in the chart

Hope this helps...

[edit]

I delete Series1 because it just graphs the months in respect to the ... months... Its series 2 I am concerned about so I only display that.

Try using

.SeriesCollection(1).XValues

and

.SeriesCollection(1).Values

together in lieu of

.SeriesCollection(1).Delete

[pgag45]

Great thanks again koolsid. I will try implementing SeriesCollection and see if that works.

[Siddharth Rout]

just edited the above post (added new comments)

[pgag45]

I'm now getting an error, saying "seriescollection method of _chart object failed ...

Sub AddChartSheet(chartName As String, setRange As String, yTitle As String, graphStyle As Integer, chartOf As Chart)
   Set chartOf = Charts.Add
   For Each shtNext In Sheets
       If shtNext.Name = chartName Then 'Search/Delete charts w/ same name
            Application.DisplayAlerts = False 'No delete prompt
            Sheets(chartName).Delete
            Application.DisplayAlerts = True
       End If
   Next shtNext
   With chartOf
     .Name = chartName
      If graphStyle = 2 Then
        .ChartType = xlColumnClustered
      ElseIf graphStyle = 1 Then
        .ChartType = xlLine
      End If
      '.SetSourceData Source:=Sheets("SOILSYM_MONTH").Range(setRange), _
        'PlotBy:=xlColumns
        .SeriesCollection.NewSeries
        .SeriesCollection(1).XValues = "=SheetName!R4C1:R14C1" 'failing here
        .SeriesCollection(1).Values = "=SheetName!R4C7:R14C7"
        
      .HasTitle = True
      .ChartTitle.Text = chartName
      .Axes(xlCategory, xlPrimary).HasTitle = True
      .Axes(xlCategory, xlPrimary).AxisTitle.Characters.Text = "Month"
      .Axes(xlValue, xlPrimary).HasTitle = True
      .Axes(xlValue, xlPrimary).AxisTitle.Characters.Text = yTitle
      '.SeriesCollection(1).Delete
      .HasLegend = False
   End With
End Sub

[pgag45]

Nevermind got it working.. thanks

[Siddharth Rout]

HI

"=SheetName!R4C1:R14C1"

was that because of

"=" & SheetName & "!R4C1:R14C1" ?

[pgag45]

yes

but thanks for the continuing help man.