Excel Macro Help for Charts

[whitethomas12]

I am getting an error message when trying to have a macro create a
chart. The following is my error message:

Run-Time Error '1004'
Unbale to set the xValue property of the Seris class

The following is my code:

Charts.Add
ActiveChart.ChartType = xlLineMarkers
ActiveChart.Location Where:=xlLocationAsObject, Name:="Sheet5"
ActiveChart.SetSourceData
Source:=Sheets("Sheet4").Range("A3:A23,C3:C23"), _
PlotBy:=xlColumns
ActiveChart.SeriesCollection(1).XValues = "=(Sheet4!A8,A15,A23)"
ActiveChart.SeriesCollection(1).Values = _
"=(Sheet4!C9,C16,C24)"
ActiveChart.SeriesCollection(1).Name = "=Sheet4!A2"

All of my cells is sheet4 have valid data. I am not sure what I am
doing wrong. The should be created in sheet5

 

[Lazzzx]

Hi,
Use R1C1 notation instead of A1 notation.
Your line will then look like this:
ActiveChart.SeriesCollection(1).XValues =
"=(Sheet4!R8C1,Sheet4!R15C1,Sheet4!R23C1)"

R8C1 refers to cell A8 - rows and columns are reversed
The next lines in your code need to be modified too.
rgds,
Lazzzx

 

[whitethomas12]

Hi,
Use R1C1 notation instead of A1 notation.
Your line will then look like this:
     ActiveChart.SeriesCollection(1).XValues =
"=(Sheet4!R8C1,Sheet4!R15C1,Sheet4!R23C1)"

R8C1 refers to cell A8 - rows and columns are reversed
The next lines in your code need to be modified too.
rgds,
Lazzzx

<[email protected]> skrev i meddelelsen







- Show quoted text -

I tried that it that did not work for some reason or another

 

[whitethomas12]

I tried that it that did not work for some reason or another- Hide quoted text -

- Show quoted text -

I made the change, but I still get the same error


Charts.Add
ActiveChart.ChartType = xlLineMarkers
ActiveChart.Location Where:=xlLocationAsObject, Name:="Sheet5"
ActiveChart.SetSourceData
Source:=Sheets("Sheet4").Range("A3:A23,C3:C23"), _
PlotBy:=xlColumns
ActiveChart.SeriesCollection(1).XValues = "=(Sheet4!R8C1:R15C1)"
',A23)"
ActiveChart.SeriesCollection(1).Values = _
"=(Sheet4!R3C3:R9C3)" 'C9,C16,C24)"
ActiveChart.SeriesCollection(1).Name = "=Sheet4!R2C1"

I decided to try to use a continuous range to make sure that there was
data and it still did not work

Thank you for your help

 

[whitethomas12]

I made the change, but I still get the same error

Charts.Add
    ActiveChart.ChartType = xlLineMarkers
    ActiveChart.Location Where:=xlLocationAsObject, Name:="Sheet5"
    ActiveChart.SetSourceData
Source:=Sheets("Sheet4").Range("A3:A23,C3:C23"), _
        PlotBy:=xlColumns
     ActiveChart.SeriesCollection(1).XValues = "=(Sheet4!R8C1:R15C1)"
',A23)"
     ActiveChart.SeriesCollection(1).Values = _
        "=(Sheet4!R3C3:R9C3)" 'C9,C16,C24)"
    ActiveChart.SeriesCollection(1).Name = "=Sheet4!R2C1"

I decided to try to use a continuous range to make sure that there was
data and it still did not work

Thank you for your help- Hide quoted text -

- Show quoted text -

I am still stuck. I tried to match the rows to see if that was the
issue and that did not work

Here is my modified code

'ActiveSheet.ChartObjects.Delete
Charts.Add
ActiveChart.ChartType = xlLineMarkers
ActiveChart.Location Where:=xlLocationAsObject, Name:="Sheet5"
ActiveChart.SetSourceData
Source:=Sheets("Sheet4").Range("A3:A23,C3:C23"), _
PlotBy:=xlColumns
ActiveChart.SeriesCollection(1).XValues = "=(Sheet4!R9C1,Sheet4!
R16C1,Sheet4!R24C1)" ',A23)"
ActiveChart.SeriesCollection(1).Values = _
"=(Sheet4!R9C3,Sheet4!R16C3,Sheet4!R24C3)" 'C9,C16,C24)"
ActiveChart.SeriesCollection(1).Name = "=Sheet4!R2C1"

Thank you

 

[Jon Peltier]

Try this:

ActiveChart.SeriesCollection(1).XValues = _
"=Sheet4!R8C1,Sheet4!R15C1,Sheet4!R23C1"
ActiveChart.SeriesCollection(1).Values = _
"=Sheet4!R8C3,Sheet4!R15C3,Sheet4!R23C3"

- Jon

 

[whitethomas12]

Try this:

    ActiveChart.SeriesCollection(1).XValues = _
        "=Sheet4!R8C1,Sheet4!R15C1,Sheet4!R23C1"
    ActiveChart.SeriesCollection(1).Values = _
        "=Sheet4!R8C3,Sheet4!R15C3,Sheet4!R23C3"

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










- Show quoted text -

I just tried that and that did not work. I am not sure what I am
doing wrong

 

[Jon Peltier]

Post on top, like most of the folks here do, to make reading the thread
easier.

Describe "did not work". Did you get an error? What was the error message
(the error number is generally useless)? On what line?

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

Try this:

ActiveChart.SeriesCollection(1).XValues = _
"=Sheet4!R8C1,Sheet4!R15C1,Sheet4!R23C1"
ActiveChart.SeriesCollection(1).Values = _
"=Sheet4!R8C3,Sheet4!R15C3,Sheet4!R23C3"

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










- Show quoted text -

I just tried that and that did not work. I am not sure what I am
doing wrong

 

[Jon Peltier]

Also, don't give up on the other post, started a few hours earlier than
this. I provided some code to make the data selection more general.

Is it possible to redesign the worksheet to use contiguous ranges? Either
rearrange the existing data range or set up a second range, linked to the
first, which connects the cells of interest. Spending five minutes with the
data will save five hours of aggravation.

- Jon