PDA

View Full Version : Unable to set the XValues property of the Series class



yerromnitsuj
09-14-2011, 11:23 AM
I am trying to debug code written a couple years ago by someone other than me. The code is below. I am trying to loop through several worksheets and update a chart on each sheet. I get the error, "Unable to set the XValues property of the Series class".

Sub Vlookup_formula_histogram()
'
'
Dim State As String
Dim chtObj As ChartObject
For i = 1 To Worksheets(SheetA).Range("B3").Value 'Number of state and company combos

If Worksheets(SheetB).Cells(i + 1, 6) = Worksheets(SheetA).Range("B5") Then

State = Worksheets(SheetB).Cells(i + 1, 1)

'Copy and drag the histogram formula
Sheets(Worksheets(SheetB).Cells(i + 1, 1).Value).Select
Range("S4").Select
ActiveCell.FormulaR1C1 = "=VLOOKUP(RC[-1],R2C69:R23C70,2)"
Range("S4").Select
Selection.AutoFill Destination:=Range("S4:S" & Sheets(Worksheets(SheetB).Cells(i + 1, 1).Value).Range("X3").Value + 3)

For Each chtObj In Sheets(State).ChartObjects

'Change the formula for the chart
chtObj.Activate
ActiveChart.SeriesCollection(1).XValues = "=" & State & "!R2C70:R23C70"
ActiveChart.SeriesCollection(1).Values = "=" & State & "!R2C71:R23C71"

Next
End If
Next i
End Sub
Here is the line that is the culprit for the error:

ActiveChart.SeriesCollection(1).XValues = "=" & State & "!R2C70:R23C70"
Any ideas of why I might get this error and how I might fix it? Thanks!

Aflatoon
09-14-2011, 01:12 PM
Does State contain a space? Safer to use:
ActiveChart.SeriesCollection(1).XValues = "='" & State & "'!R2C70:R23C70"