Setting Charts Property through VBA code

gnaga

Well-known Member
Joined
Jul 9, 2002
Messages
700
Office Version
  1. 365
  2. 2016
  3. 2013
Platform
  1. Windows
Run Time Error '1004'
Unable to set the values property of series class

I am getting the aove error whiel setting the Y axis value range of my chart.

This is my code. I am not getting the error while setting the XValue property. Content in the valriable Path & FileName is same for both X & Y

Path = "D:My StockPricePriceListA
FileName = "Aarti Ind.xls"


With ActiveChart
.SeriesCollection(1).XValues = _
"'" & Path & "[" & FileName & "]Sheet1'!$F$2:$F$" & XRow
.SeriesCollection(2).XValues = _
"'" & Path & "[" & FileName & "]Sheet1'!$F$2:$F$" & XRow
.SeriesCollection(1).Values = _
"'[" & FileName & "]Sheet1'!$G$2:$G$" & XRow
.SeriesCollection(2).Values = _
"'" & Path & "[" & FileName & "]Sheet1'!$B$2:$B$" & XRow
.ChartTitle = Left(FileName, Len(FileName) - 4)
End With

Thanks

GNaga
 

Excel Facts

What is =ROMAN(40) in Excel?
The Roman numeral for 40 is XL. Bill "MrExcel" Jelen's 40th book was called MrExcel XL.

gnaga

Well-known Member
Joined
Jul 9, 2002
Messages
700
Office Version
  1. 365
  2. 2016
  3. 2013
Platform
  1. Windows
Sorry once I have tried with direct filename that was also not woking. I have pasted that code.

Even with this I am getting the same error

With ActiveChart
.SeriesCollection(1).XValues = _
"'" & Path & "[" & FileName & "]Sheet1'!$F$2:$F$" & XRow
.SeriesCollection(2).XValues = _
"'" & Path & "[" & FileName & "]Sheet1'!$F$2:$F$" & XRow
.SeriesCollection(1).Values = _
"'[" & Path & "[" & FileName & "]Sheet1'!$G$2:$G$" & XRow
.SeriesCollection(2).Values = _
"'" & Path & "[" & FileName & "]Sheet1'!$B$2:$B$" & XRow
.ChartTitle = Left(FileName, Len(FileName) - 4)
End With
 

Andrew Poulsom

MrExcel MVP
Joined
Jul 21, 2002
Messages
73,092
Strangely, if I record a macro to set the values of a series Excel uses R1C1 style notation like this:

Code:
ActiveChart.SeriesCollection(1).Values = "=Sheet1!R1C2:R6C2"

If I try to use non R1C1 references like this:

Code:
ActiveChart.SeriesCollection(1).Values = "=Sheet1!B1:B6"

I get an error.

But I can pass it a Range object like this:

Code:
Sub Test()
    Dim Rng1 As Range
    Dim Rng2 As Range
    Set Rng1 = Worksheets("Sheet1").Range("B1:B6")
    Set Rng2 = Workbooks("BOOK2").Worksheets("Sheet1").Range("C1:C6")
    ActiveChart.SeriesCollection(1).Values = Rng1
    ActiveChart.SeriesCollection(2).Values = Rng2
End Sub

Hope this helps.
 

gnaga

Well-known Member
Joined
Jul 9, 2002
Messages
700
Office Version
  1. 365
  2. 2016
  3. 2013
Platform
  1. Windows
Thank you so much Andrew, The second option of using range object is well sufficient to my requirement. Thanks again

GNaga
 

Forum statistics

Threads
1,143,920
Messages
5,721,541
Members
422,369
Latest member
redinator

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top