Thread: Excel 2007 to 2003 VBA Compatability - Modifying Coding to comply with 2003 Interface

Hi All,

First time poster, long-time viewer (different username).

I am experiencing an issue while running a macro in Excel 2003 ("Runtime error '1004' - unable to set the values property of the Series class"). The code was originally designed and used in Excel 2007 which runs without error. Due to software requirements at work, I need to convert the VBA coding to be compatible with Excel 2003.

When running the macro, a part of the code labeled "SeriesCollection" appears to be the root of the problem. I have not been able to find a work-around in the forum or from my Company's IS team, so any VBA advice to get me started would be greatly appreciated! Please feel free to reference the Waterfall worksheet uploaded in .xls (converted to Excel 2003 format).

Below is the code used in the worksheet.

Sub change_sourceRow()
'
' change_sourceRow Makro


Dim Range As String
Dim lastRow, i As Long
Const FIRSTROW = 7
Const NUM_Series = 10
Dim Series(10, 2) As String

'initialize series
Series(1, 1) = "H"
Series(2, 1) = "I"
Series(3, 1) = "J"
Series(4, 1) = "K"
Series(5, 1) = "L"
Series(6, 1) = "M"
Series(7, 1) = "N"
Series(8, 1) = "P"
Series(9, 1) = "Q"
Series(10, 1) = "R"



'Define Range
    lastRow = ActiveSheet.Range("C5").Value + FIRSTROW - 1
    

'Change charts
    Sheets("Waterfall").Select
    ActiveSheet.ChartObjects("Diagramm 1").Activate

    For i = 1 To NUM_Series
        Range = "='Waterfall'!$" & Series(i, 1) & "$" & FIRSTROW & ":$" & Series(i, 1) & "$" & lastRow
        ActiveChart.SeriesCollection(i).Values = Range
 
    Next


End Sub
-----------------------------------------------------------------------------------------------------------------------------
Sub Makro5()
'
' Makro5 Makro
'

'
    ActiveChart.SeriesCollection(1).Values = "='Waterfall'!$H$" & FIRSTROW & ":$H$" & lastRow
    ActiveChart.SeriesCollection(2).Values = "='Waterfall'!$I$6:$I$" & lastRow
    ActiveChart.SeriesCollection(3).Values = "='Waterfall'!$J$6:$J$" & lastRow
    ActiveChart.SeriesCollection(4).Values = "='Waterfall'!$K$6:$K$" & lastRow
    ActiveChart.SeriesCollection(5).Values = "='Waterfall'!$L$6:$L$" & lastRow
    ActiveChart.SeriesCollection(6).Values = "='Waterfall'!$M$6:$M$" & lastRow
    ActiveChart.SeriesCollection(7).Values = "='Waterfall'!$N$6:$N$" & lastRow
    ActiveChart.SeriesCollection(8).Values = "='Waterfall'!$P$6:$P$" & lastRow
    ActiveChart.SeriesCollection(9).Values = "='Waterfall'!$Q$6:$Q$" & lastRow
    ActiveChart.SeriesCollection(10).Values = "='Waterfall'!$R$6:$R$" & lastRow
End Sub

Probably it's because your formula for Range is not in R1C1 notation. You could either:

1. Convert the formula so it generates an address R1C1 notation.

2. Use instead

        Dim rRange As Range ' not as String
        Set rRange = Range("'Waterfall'!$" & Series(i, 1) & "$" & FIRSTROW & ":$" & Series(i, 1) & "$" & lastRow)

The second approach requires less recoding.

Also, it's not a good idea to use a keyword ("Range") as a variable name.

Probably this suffices:

 
Sub change_sourceRow_snb()
 For j = 1 To 10
  Sheets("Waterfall").ChartObjects("Diagramm 1").Chart.SeriesCollection(j).Values = Sheets("Waterfall").Range(Replace("$~$7:$~$", "~", Chr(72 + j + IIf(j > 7, 1, 0))) & Cells(5, 3) + 6)
 Next
end sub