Setting Charts Property through VBA code

[gnaga]

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

 

[Andrew Poulsom]

Missing Path in line 3?

 

[gnaga]

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]

Strangely, if I record a macro to set the values of a series Excel uses R1C1 style notation like this:

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

If I try to use non R1C1 references like this:

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

I get an error.

But I can pass it a Range object like this:

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]

Thank you so much Andrew, The second option of using range object is well sufficient to my requirement. Thanks again

GNaga