[RESOLVED] [Excel 2003] Increment New Chart Series

[campster]

I have a workbook that already contains numerous charts. I now want to add a new series to each chart. The source data for the new series will come from a worksheet in which the columns align with the charts. for example the first chart will use column B for its source, the second will use column C for its source and so on. All charts will use column A as the x axis data.

The code below works for the first chart but I can't figure out how to increment to the next columns for the remaining charts. Any ideas?

For Each oXLChart In oXLBookCurrent.Charts
            With oXLChart
                iRow = oXLSheetBase.UsedRange.Rows.Count
                With .SeriesCollection.NewSeries
                    .XValues = oXLSheetBase.Range("A3:A" & iRow)
                    .Values = oXLSheetBase.Range("B3:B" & iRow) 'This line needs to change
                    .Name = aBase(iBase)
                    .MarkerStyle = xlMarkerStyleNone
                End With
            End With
        Next

[Siddharth Rout]

Instead of using Range("B3:B" & iRow), use the cells notation and then put then in the loop for example

'Range("B3:B" & iRow) can be written as
Range(Cells(3, 2), Cells(iRow, 2))

'Range("C3:C" & iRow) can be written as
Range(Cells(3, 3), Cells(iRow, 3))

'Range("D3:D" & iRow) can be written as
Range(Cells(3, 4), Cells(iRow, 4))

So in a loop it will look something like this

'~~> Looping from Col B to Col E for example
For i = 2 To 5
    '~~ Rest of the code
    .Values = oXLSheetBase.Range(Cells(3, i), Cells(iRow, i))
    '~~ Rest of the code
Next

[campster]

I tried that already but get a run time error: "Application-defined or object-defined error". Here's the entire routine

Private Sub btnImportBase_Click()
 
Dim oXLApp As Excel.Application
Dim oXLBookTemp As Excel.Workbook
Dim oXLBookCurrent As Excel.Workbook
Dim oXLSheetControl As Excel.Worksheet
Dim oXLSheetBase As Excel.Worksheet
Dim oXLSheetTemp As Excel.Worksheet
Dim oXLChart As Excel.Chart
Dim iBase As Integer
Dim sBase As String
Dim aBase(5) As String
Dim iCol As Integer
Dim iRow As Integer
Dim iSeriesCnt As Integer
 
aBase(1) = "Base1"
aBase(2) = "Base2"
aBase(3) = "Base3"
aBase(4) = "Base4"
aBase(5) = "Base5"
 
Set oXLBookCurrent = ThisWorkbook
Set oXLSheetControl = oXLBookCurrent.Worksheets("Control")
 
Set oXLApp = New Excel.Application
oXLApp.Visible = False   'Visible for debug only
oXLApp.DisplayAlerts = False
 
For iBase = 1 To 5
    sBase = oXLSheetControl.Cells(8 + iBase, 2).Value  'Contains path of file to open
    If Not sBase = "" Then   'If path exists
            
        Set oXLBookTemp = oXLApp.Workbooks.Open(sBase)  'Open CSV file
        Set oXLSheetTemp = oXLBookTemp.Worksheets(1)    'Define worksheet
        
        'define worksheet in current book based on index iBase
        Set oXLSheetBase = oXLBookCurrent.Worksheets(aBase(iBase))
       
        oXLSheetTemp.UsedRange.Copy
        oXLSheetBase.Range("A1").PasteSpecial Paste:=xlPasteValues
        
        For Each oXLChart In oXLBookCurrent.Charts
            With oXLChart
                iRow = oXLSheetBase.UsedRange.Rows.Count
                With .SeriesCollection.NewSeries
                    .XValues = oXLSheetBase.Range("A3:A" & iRow)
                    .Values = oXLSheetBase.Range(Cells(3, 2), Cells(iRow, 2))
                    .Name = aBase(iBase)
                    .MarkerStyle = xlMarkerStyleNone
                End With
            End With
        Next
    
    Else
    
        oXLBookCurrent.Worksheets(aBase(iBase)).Visible = False
        
    End If
Next iBase
 
oXLApp.Quit
 
Set oXLSheetTemp = Nothing
Set oXLSheetBase = Nothing
Set oXLBookCurrent = Nothing
Set oXLBookTemp = Nothing
Set oXLApp = Nothing
 
End Sub

[Siddharth Rout]

Ok Try this

.Values = oXLSheetBase.Range(Replace(oXLSheetBase.Cells(3, 2).Address, "$", ""), _
            Replace(oXLSheetBase.Cells(iRow, 2).Address, "$", ""))

[campster]

Hmmm. That got it but I have no idea why. I guess that there were unneeded "$" characters ??

Thanks for the help.