Dynamic Excel Chart generation problem

[dydhda]

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" & Trim(CStr(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>

 

[SkipVought]

Works as long as there's valid data in each range...

Sub test()
    Dim i As Integer, iWid As Integer, iHgt As Integer
    Dim Count As Integer
    Dim intGraphDisplayStartRow As Integer
    Dim rngTempRange1 As Range
    Dim rngTempRange2 As Range
    Dim rngTempRange3 As Range
    
    
    iWid = 100
    iHgt = 100
    
    Count = 1
    If Count > 0 Then
    
        For i = 1 To Count
        
            intGraphDisplayStartRow = (i * 13) + 2
            
            Charts.Add
            With ThisWorkbook.Worksheets("WorkspaceTemp")
                Set rngTempRange1 = .Range(.Cells(intGraphDisplayStartRow, "A"), .Cells(intGraphDisplayStartRow + 11, "A"))
                
                Set rngTempRange2 = .Range(.Cells(intGraphDisplayStartRow, "C"), .Cells(intGraphDisplayStartRow + 11, "C"))
                
                Set rngTempRange3 = .Range(.Cells(intGraphDisplayStartRow - 1, "C"))
            End With
            With ActiveChart
                .ChartType = xlLine
                .SeriesCollection(1).XValues = rngTempRange1
                .SeriesCollection(1).Values = rngTempRange2
                .SeriesCollection(1).Name = rngTempRange3.Value
                .Location Where:=xlLocationAsObject, Name:="Page 2"
            End With
            With ActiveChart
                'position each chart
                If i = 1 Then
                    .Top = 0
                    .Left = 0
                Else
                    With Worksheets("Page 2").Shapes(i - 1)
                        ActiveChart.Left = .Left + .Width
                    End With
                End If
            End With
        Next
    
    End If
    Worksheets("Page 2").Activate
End Sub

Skip,
[sub]
[red]A palindrome gone wrong?[/red]
A man, a plan, a ROOT canal...
PULLEMALL![/sub]