Err#1004 "Unable to set the XValues property of the Series class"

[Tekito]

This is an issue that I've discusses many times before, but never with a conclusive solution. I am trying to programmatically change the data range for an existing chart in Excel. My code goes something like this:

With WS.CharObjects(SelChart).Chart
  With .SeriesCollection(1)
    .XValues =DataWS.Range("A1:A101")   '<-----ERROR!
    .Values = DataWS.Range("B1:B101")
  End With
End With

The .XValues line results in the "1004 Unable to set the XValues property of the Series class" error. The range contains valid data: numbers, no blanks. When I manually set the chart to this range, everything is fine. Like I said, I've read many people having this problem, but nothing is working for me. I've had no end of trouble dealing with Excel charts. Sometimes they work, sometimes they don't.

[QVeen72]

Hi,

I Guess DataWS is not set to the referring Worksheet...

Try this :

With WS.CharObjects(SelChart).Chart
  With .SeriesCollection(1)
    .XValues =Sheets("Sheet1").Range("A1:A101")   
    .Values = Sheets("Sheet1").Range("B1:B101")
  End With
End With

Regards
Veena

[Tekito]

The data columns and graph are on two different worksheets. I have tried explicitly naming the worksheets (which shouldn't make any difference) and other trivial syntax variations, but nothing works.

[QVeen72]

Hi,

Well.. I Guess, after Googling with the error said, it appears that, it is a BUG in Excell 2k. Not sure, if the higher versions of Excel have been cleared of that bug..

Regards
Veena

[Tekito]

Yeah, so it appears... I've pretty much resigned to finding workarounds. I really am not very fond of Excel charts.

[shikeh]

Hi
I found that this bug cause the program not working only when you do a presentation in front of managers. then it crash :(
but my solution for this is to run-over all the range (or array, in my case) and make it a string
this is the code for the string (pointing every time on Reported (N):

ReportedString = ReportedString & Chr(&H22) & Reported(N) & Chr(&H22) & ","

we need this Chr function because we need to insert string as the Xvalue (and we can just like that add ' " ')
after that, remove the last "," by this code:

ReportedString = Left(ReportedString, Len(ReportedString) - 1)

then, the last one is to add the brackets:

ReportedString = "={" & ReportedString & "}"

after this, just:

.XValues= ReportedString

Hope it will help you (it helpd me :) )
Shikeh

[gmcfarlane]

HI

I am reworking a spreadsheet someone else wrote, sigh....
I was getting this error pretty consistently trying to assing XValues

When i went back to the original, i realized my recordset was offset by 1 column
The XValues are defined in 2 columns, year quarters in B and groups in C.

What seems to cause the problem is that when i shifter the result set left , the column which should have been populated w/ unique values now has duplicates in it.

I can reproduce or eliminate the error at will by pointing at the correct or incorrect columns.

Dont know if this applies to everyone in this thread, but this was the issue for me.