Dynamic Chart Generation

I am trying to use VBA to dynamically produce a number of charts. The number of charts produced depends on the amount of data that appears in a seperate spreadsheet...

I have taken the code produced when I recorded a macro to produce one such chart and adapted it to meet my needs, however I am getting error 1004, "Unable to set the XValues property of the Series class" when I try to assign the x-axis values using a range I have dynamically created.

I'm sure the problem is somewhere with my syntax, but however I try to phrase it I cannot seem to get it to work.

Please find a copy of my code below - any help would be much appreciated!

Jon

In the following code the variable Count determines how many charts will need to be produced:

<code>
Dim i As Integer
Dim Count As Integer
Dim intGraphDisplayStartRow As Integer
Dim rngTempRange1 As Range
Dim rngTempRange2 As Range
Dim rngTempRange3 As Range


If Count > 0 Then
For i = 1 To Count
intGraphDisplayStartRow = (i * 13) + 2
ThisWorkbook.Worksheets("Page 2").Select

Charts.Add
Set rngTempRange1 = ThisWorkbook.Worksheets("WorkspaceTemp") _
.Range("A" & Trim(CStr(intGraphDisplayStartRow _
& ":A" & intGraphDisplayStartRow + 11)))
Set rngTempRange2 = ThisWorkbook.Worksheets("WorkspaceTemp") _
.Range("C" & Trim(CStr(intGraphDisplayStartRow)) & ":C" _
& Trim(CStr(intGraphDisplayStartRow + 11)))
Set rngTempRange3 = ThisWorkbook.Worksheets("WorkspaceTemp") _
.Range("C" & Trim(CStr(intGraphDisplayStartRow - 1)))

ActiveChart.ChartType = xlLine
ActiveChart.SeriesCollection(1).XValues = rngTempRange1
ActiveChart.SeriesCollection(1).Values = rngTempRange2
ActiveChart.SeriesCollection(1).Name = rngTempRange3
ActiveChart.Location Where:=xlLocationAsObject, Name:="Page 2"

Next
End If
</code>

Hi jonwatts,

The only thing wrong with your code is that when you create a new chart
Series(1) does not exist, so you need to add a new series. Try this
slightly modified version.

Dim i As Integer
Dim Count As Integer
Dim iStartRow As Integer
Dim iEndRow As Integer
Dim rngTempRange1 As Range
Dim rngTempRange2 As Range
Dim rngTempRange3 As Range
Dim wks As Worksheet

' Just for neatness, and so I could test on my own
' worksheet, we set a variable for the worksheet
Count = 2
Set wks = ThisWorkbook.Worksheets("WorkspaceTemp")

If Count > 0 Then
For i = 1 To Count
iStartRow = (i * 13) + 2
iEndRow = iStartRow + 11 ' iEndRow calculated here, makes the range
calcs shorter
ThisWorkbook.Worksheets("Page 2").Select

Set rngTempRange1 = wks.Range("A" & Trim(CStr(iStartRow & ":A" &
iEndRow)))
Set rngTempRange2 = wks.Range("C" & Trim(CStr(iStartRow)) & ":C" &
Trim(CStr(iEndRow)))
Set rngTempRange3 = wks.Range("C" & Trim(CStr(iStartRow - 1)))

Charts.Add
With ActiveChart
.ChartType = xlLine
.SeriesCollection.Add rngTempRange2 ' the only change you really
need
.SeriesCollection(1).XValues = rngTempRange1
.SeriesCollection(1).Name = rngTempRange3
.Location Where:=xlLocationAsObject, Name:="Page 2"
End With
ActiveChart.Parent.Top = iStartRow * 5
ActiveChart.Parent.Left = iStartRow * 5
Next
End If

Ed Ferrero
http://edferrero.m6.net/

> I am trying to use VBA to dynamically produce a number of charts. The
> number of charts produced depends on the amount of data that appears in
> a seperate spreadsheet...
>
> I have taken the code produced when I recorded a macro to produce one
> such chart and adapted it to meet my needs, however I am getting error
> 1004, "Unable to set the XValues property of the Series class" when I
> try to assign the x-axis values using a range I have dynamically
> created.
>
> I'm sure the problem is somewhere with my syntax, but however I try to
> phrase it I cannot seem to get it to work.
>
> Please find a copy of my code below - any help would be much
> appreciated!
>
> Jon
>
> In the following code the variable Count determines how many charts
> will need to be produced:
>
> <code>
> Dim i As Integer
> Dim Count As Integer
> Dim intGraphDisplayStartRow As Integer
> Dim rngTempRange1 As Range
> Dim rngTempRange2 As Range
> Dim rngTempRange3 As Range
>
>
> If Count > 0 Then
> For i = 1 To Count
> intGraphDisplayStartRow = (i * 13) + 2
> ThisWorkbook.Worksheets("Page 2").Select
>
> Charts.Add
> Set rngTempRange1 =
> ThisWorkbook.Worksheets("WorkspaceTemp") _
> Range("A" & Trim(CStr(intGraphDisplayStartRow _
> & ":A" & intGraphDisplayStartRow + 11)))
> Set rngTempRange2 =
> ThisWorkbook.Worksheets("WorkspaceTemp") _
> Range("C" & Trim(CStr(intGraphDisplayStartRow)) & ":C"
> _
> & Trim(CStr(intGraphDisplayStartRow + 11)))
> Set rngTempRange3 =
> ThisWorkbook.Worksheets("WorkspaceTemp") _
> Range("C" & Trim(CStr(intGraphDisplayStartRow - 1)))
>
> ActiveChart.ChartType = xlLine
> ActiveChart.SeriesCollection(1).XValues = rngTempRange1
> ActiveChart.SeriesCollection(1).Values = rngTempRange2
> ActiveChart.SeriesCollection(1).Name = rngTempRange3
> ActiveChart.Location Where:=xlLocationAsObject, Name:="Page
> 2"
>
> Next
> End If
> </code>
>
>
> --
> jonwatts
> ------------------------------------------------------------------------
> jonwatts's Profile:
> http://www.excelforum.com/member.php...o&userid=28038
> View this thread: http://www.excelforum.com/showthread...hreadid=475451
>

A further refinement is to use

Worksheets("Page 2").ChartObjects.Add(<dimensions>)

rather than

Charts.Add
...
ActiveChart.Location Where:=xlLocationAsObject, Name:="Page 2"

Work your position parameters

> ActiveChart.Parent.Top = iStartRow * 5
> ActiveChart.Parent.Left = iStartRow * 5

into the left, top, width, and height dimensions in the ChartObjects.Add
statement.

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Peltier Technical Services
Tutorials and Custom Solutions
http://PeltierTech.com/
_______


Ed Ferrero wrote:

> Hi jonwatts,
>
> The only thing wrong with your code is that when you create a new chart
> Series(1) does not exist, so you need to add a new series. Try this
> slightly modified version.
>
> Dim i As Integer
> Dim Count As Integer
> Dim iStartRow As Integer
> Dim iEndRow As Integer
> Dim rngTempRange1 As Range
> Dim rngTempRange2 As Range
> Dim rngTempRange3 As Range
> Dim wks As Worksheet
>
> ' Just for neatness, and so I could test on my own
> ' worksheet, we set a variable for the worksheet
> Count = 2
> Set wks = ThisWorkbook.Worksheets("WorkspaceTemp")
>
> If Count > 0 Then
> For i = 1 To Count
> iStartRow = (i * 13) + 2
> iEndRow = iStartRow + 11 ' iEndRow calculated here, makes the range
> calcs shorter
> ThisWorkbook.Worksheets("Page 2").Select
>
> Set rngTempRange1 = wks.Range("A" & Trim(CStr(iStartRow & ":A" &
> iEndRow)))
> Set rngTempRange2 = wks.Range("C" & Trim(CStr(iStartRow)) & ":C" &
> Trim(CStr(iEndRow)))
> Set rngTempRange3 = wks.Range("C" & Trim(CStr(iStartRow - 1)))
>
> Charts.Add
> With ActiveChart
> .ChartType = xlLine
> .SeriesCollection.Add rngTempRange2 ' the only change you really
> need
> .SeriesCollection(1).XValues = rngTempRange1
> .SeriesCollection(1).Name = rngTempRange3
> .Location Where:=xlLocationAsObject, Name:="Page 2"
> End With
> ActiveChart.Parent.Top = iStartRow * 5
> ActiveChart.Parent.Left = iStartRow * 5
> Next
> End If
>
> Ed Ferrero
> http://edferrero.m6.net/
>
>
>>I am trying to use VBA to dynamically produce a number of charts. The
>>number of charts produced depends on the amount of data that appears in
>>a seperate spreadsheet...
>>
>>I have taken the code produced when I recorded a macro to produce one
>>such chart and adapted it to meet my needs, however I am getting error
>>1004, "Unable to set the XValues property of the Series class" when I
>>try to assign the x-axis values using a range I have dynamically
>>created.
>>
>>I'm sure the problem is somewhere with my syntax, but however I try to
>>phrase it I cannot seem to get it to work.
>>
>>Please find a copy of my code below - any help would be much
>>appreciated!
>>
>>Jon
>>
>>In the following code the variable Count determines how many charts
>>will need to be produced:
>>
>><code>
>>Dim i As Integer
>>Dim Count As Integer
>>Dim intGraphDisplayStartRow As Integer
>>Dim rngTempRange1 As Range
>>Dim rngTempRange2 As Range
>>Dim rngTempRange3 As Range
>>
>>
>>If Count > 0 Then
>>For i = 1 To Count
>>intGraphDisplayStartRow = (i * 13) + 2
>>ThisWorkbook.Worksheets("Page 2").Select
>>
>>Charts.Add
>>Set rngTempRange1 =
>>ThisWorkbook.Worksheets("WorkspaceTemp") _
>>Range("A" & Trim(CStr(intGraphDisplayStartRow _
>>& ":A" & intGraphDisplayStartRow + 11)))
>>Set rngTempRange2 =
>>ThisWorkbook.Worksheets("WorkspaceTemp") _
>>Range("C" & Trim(CStr(intGraphDisplayStartRow)) & ":C"
>>_
>>& Trim(CStr(intGraphDisplayStartRow + 11)))
>>Set rngTempRange3 =
>>ThisWorkbook.Worksheets("WorkspaceTemp") _
>>Range("C" & Trim(CStr(intGraphDisplayStartRow - 1)))
>>
>>ActiveChart.ChartType = xlLine
>>ActiveChart.SeriesCollection(1).XValues = rngTempRange1
>>ActiveChart.SeriesCollection(1).Values = rngTempRange2
>>ActiveChart.SeriesCollection(1).Name = rngTempRange3
>>ActiveChart.Location Where:=xlLocationAsObject, Name:="Page
>>2"
>>
>>Next
>>End If
>></code>
>>
>>
>>--
>>jonwatts
>>------------------------------------------------------------------------
>>jonwatts's Profile:
>>http://www.excelforum.com/member.php...o&userid=28038
>>View this thread: http://www.excelforum.com/showthread...hreadid=475451
>>
>
>
>