Run-time error '1004' Unable to set the XValues property of the series class

[forgedascendant]

Good day everyone,

I am new to this site so please forgive me if this post isn't completly correct. For the past 2 weeks I have been beating my head against the wall trying to figure out what is wrong with the macro I created. The problem I am experiancing is that the range I am trying to put together does not seem to be working for some reason. My entire code is as follows:

Sub Proto1()
'
' Proto1 Macro
' Macro recorded 8/30/2007 by aaron.verellen
'
 
'
    Dim CurrentSheet As String, ChartName As String
    Dim RowCount As Integer, ColumnCount As Integer, FirstColumn As Integer, LastColumn, _
        FirstRow As Integer, LastRow As Integer, Row As Integer, Column As Integer, _
        ProductColumn As Integer, ArrayIndex As Integer, ParameterCount As Integer, _
        ItemCount As Integer, MyIndex As Integer
    Dim UserProductFlag As Boolean, NewProductFlag As Boolean
    Dim Possibilities() As String
    Dim ProductXRange As Range, ProductYRange As Range
 
    'Initalize public/global values
    LockProduct = False
 
    'Identify unique products and place into an array
    ArrayIndex = 1
    ReDim Preserve Possibilities(ArrayIndex) As String
    Possibilities(ArrayIndex - 1) = "All"
 
    For Each wsheet In ThisWorkbook.Worksheets
        'CurrentSheet = wsheet.Name
 
        'verify the sheet is a data sheet
        If wsheet.Name Like "* Raw" Then
 
            'find first and last columns
            ColumnCount = 1
            Do Until Len(Sheets(wsheet.Name).Cells(2, ColumnCount + 1).Value) = 0 And Len(Sheets(wsheet.Name).Cells(2, ColumnCount + 2).Value) = 0
                If Sheets(wsheet.Name).Cells(2, ColumnCount).Value Like "Product" Then
 
                    'construct vector of possible answers
                    p = 3
                    Do Until Len(Sheets(wsheet.Name).Cells(p, ColumnCount).Value) = 0
                        NewProductFlag = False
                        For Each Item In Possibilities
                            If Item Like Sheets(wsheet.Name).Cells(p, ColumnCount).Value Then
                                NewProductFlag = True
                            End If
                        Next
 
                        If NewProductFlag = False Then
                            ArrayIndex = ArrayIndex + 1
                            ReDim Preserve Possibilities(ArrayIndex - 1) As String
                            Possibilities(ArrayIndex - 1) = Sheets(wsheet.Name).Cells(p, ColumnCount).Value
                        End If
                        p = p + 1
                    Loop
 
                End If
                ColumnCount = ColumnCount + 1
            Loop
        End If
    Next
 
    'Cycle through charts finding coresponding worksheets
    'x = 0
    'p = 0
    'For xxx = 1 To Sheets.Count
    '    If TypeName(Sheets(xxx)) = "Chart" Then
    For Each csheet In ThisWorkbook.Charts
            'ChartName = csheet.Name
 
            'get the name of the worksheet being used
            'For x = 1 To Sheets.Count
            '    If TypeName(Sheets(x)) = "Worksheet" Then
            For Each wsheet In ThisWorkbook.Worksheets
                    'CurrentSheet = wsheet.Name
 
                    'verify the sheet is a data sheet
                    If wsheet.Name Like "* Raw" Then
 
                        'find first and last columns
                        ColumnCount = 1
                        FirstColumn = 0
                        LastColumn = 0
                        Do Until Len(Sheets(wsheet.Name).Cells(2, ColumnCount + 1).Value) = 0 And Len(Sheets(wsheet.Name).Cells(2, ColumnCount + 2).Value) = 0
                            'first column
                            If Sheets(wsheet.Name).Cells(1, ColumnCount).Value Like csheet.Name Then
                                FirstColumn = ColumnCount
                            'last column
                            ElseIf Len(Sheets(wsheet.Name).Cells(2, ColumnCount + 1).Value) = 0 Then
                                LastColumn = ColumnCount
                            End If
                            If FirstColumn <> 0 And LastColumn <> 0 Then Exit Do
                            ColumnCount = ColumnCount + 1
                        Loop
 
                        'find last row
                        RowCount = 2
                        'CurrentSheet = Sheets(wsheet.Name).Cells(RowCount, FirstColumn).Value
                        Do Until Len(Sheets(wsheet.Name).Cells(RowCount, FirstColumn).Value) = 0
                            RowCount = RowCount + 1
                        Loop
                        LastRow = RowCount - 1
 
                        'figure out product column
                        ColumnCount = FirstColumn
                        Do
                            ColumnCount = ColumnCount + 1
                        Loop Until Sheets(wsheet.Name).Cells(2, ColumnCount).Value Like "Product"
                        ProductColumn = ColumnCount
 
'////////////////////////////////////////////////////////////////////////////////////////////
'this is to be added to code to input in the product name
'mid(instr(1,[product string],"+"),1,len([product string])-instr(1,[product string],"+"))
'////////////////////////////////////////////////////////////////////////////////////////////
 
                        If Not LockProduct Then
                            UserProductFlag = False
                            Do
                                'get user input
                                With ProductSelection.ComboBox1
                                    For Each Item In Possibilities
                                        .AddItem Item
                                    Next
                                End With
                                ProductSelection.Show
 
                                'make sure user input is good
                                For Each Item In Possibilities
                                    If Item Like Criteria Then UserProductFlag = True
                                Next
                            Loop Until UserProductFlag = True
                        End If
 
                        'graph
                        ParameterCount = 1
                        Do While (ParameterCount <= Sheets(csheet.Name).SeriesCollection.Count)
                            'select proper product ranges to graph
                            ItemCount = 0
                            MyIndex = LastRow
                            Do
                                If Criteria = "All" Then
                                    If ItemCount = 0 Then
                                        Set ProductXRange = Sheets(wsheet.Name).Cells(MyIndex, 3)
                                        Set ProductYRange = Sheets(wsheet.Name).Cells(MyIndex, ParameterCount + 6)
                                    Else
                                        Set ProductXRange = Union(ProductXRange, Sheets(wsheet.Name).Cells(MyIndex, 3))
                                        Set ProductYRange = Union(ProductYRange, Sheets(wsheet.Name).Cells(MyIndex, ParameterCount + 6))
                                    End If
                                    ItemCount = ItemCount + 1
                                ElseIf Sheets(wsheet.Name).Cells(MyIndex, ProductColumn).Value = Criteria Then
                                    If ItemCount = 0 Then
                                        Set ProductXRange = Sheets(wsheet.Name).Cells(MyIndex, 3)
                                        Set ProductYRange = Sheets(wsheet.Name).Cells(MyIndex, ParameterCount + 6)
                                    Else
                                        Set ProductXRange = Union(ProductXRange, Sheets(wsheet.Name).Cells(MyIndex, 3))
                                        Set ProductYRange = Union(ProductYRange, Sheets(wsheet.Name).Cells(MyIndex, ParameterCount + 6))
                                    End If
                                    ItemCount = ItemCount + 1
                                End If
                                MyIndex = MyIndex - 1
                            Loop Until ItemCount = 35 Or MyIndex = 2
 
                            'convert to a column graph
                            Sheets(csheet.Name).ChartType = xlColumnClustered
                            With Sheets(csheet.Name).SeriesCollection(ParameterCount)
                                .XValues = ProductXRange
                                .Values = ProductYRange
                            End With
                            'convert back to a line graph
                            Sheets(csheet.Name).ChartType = xlLineMarkers
                            ParameterCount = ParameterCount + 1
                        Loop
                    End If
                'End If
            Next
            'Next x
        'End If
    'Next xxx
    Next
End Sub
 

The error strikes toward the end of the code on line 163:
I am not sure but I think the problem has to do with the actual range that I am trying to put in for the XValues. Like it doesn't exist or something. Does anyone know what is wrong?

Ok...the problem through talking with coworkers and experimenting is that I am not using a continuous range. When I manually put in ranges I can utilize non continuous ranges however when using a macro that is not so. Does anyone know of a way to get around this problem?

Thanks,
Aaron