Excel VBA Chart SeriesCollection cycle through rows of data 1

[godfrey62]

I have four charts on a worksheet. Each chart has three series of data as in the source data (a separate worksheet for each chart). I need to have code that will look at the source data, create the four charts for a certain company, then go to the next row of data in each worksheet of source data and create four new charts, until the end of the data is reached (in this case, 183 companies).

I recorded a Macro to begin to see how the process would work, but when I try to replicate the code from the Macro I get an error message about the object not working with the property noted.

Here's the basic info in the macro:

ActiveSheet.ChartObjects("Chart 1&quot.Activate
ActiveChart.PlotArea.Select
ActiveChart.SeriesCollection(1).Values = _
"=(mktblTestDREMembrProvider!R3C4,mktblTestDREMembrProvider! _
R3C7,mktblTestDREMembrProvider!R3C10)"
ActiveChart.SeriesCollection(2).Values = _
"=(mktblTestDREMembrProvider!R2C3,mktblTestDREMembrProvider! _
R2C6,mktblTestDREMembrProvider!R2C9)"
ActiveChart.SeriesCollection(3).Values = _
"=(mktblTestDREMembrProvider!R3C5,mktblTestDREMembrProvider! _
R3C8,mktblTestDREMembrProvider!R3C11)"

the R#C# is the Row and Column where the data is found. I need to cycle through each Row while keeping the Column the same.

Here is what I have further attempted:


Sub UpdateCharts()

Dim i As Integer
'********** THE FOLLOWING IS FOR THE DRE CHART, CHART 1 *********
'********** THIS IS FOR THE REPORTS TAB ************************
Dim R1a As Integer 'same row in the data, different columns
'--------------------------------All The tests use the same thing---------------------

R1a = 2 'Holds row number for first row

Do Until i = 185 '183 COMPANYIES IN THIS TIME (FEB 2002)
i = 1

Worksheets("report&quot.Activate
With Worksheets("report&quot


With .ChartObjects(2) 'HbA1c

.Activate

ActiveChart.PlotArea.Select
'.PlotArea.Select
.SeriesCollection(1).Values = _
"=(mktblTestHbA1cMembrProvider!R&'R1a'&C4, mktblTestHbA1cMembrProvider!R& 'R1a'&C7, mktblTestHbA1cMembrProvider!R&'R1a'&C10)"
.SeriesCollection(2).Values = _
"=(mktblTestHbA1cMembrProvider!R&'R1a'&C3, mktblTestHbA1cMembrProvider!R& 'R1a'&C6, mktblTestHbA1cMembrProvider!R&'R1a'&C9)"
.SeriesCollection(3).Values = _
"=(mktblTestHbA1cMembrProvider!R&'R1a'&C5, mktblTestHbA1cMembrProvider!R& 'R1a'&C8, mktblTestHbA1cMembrProvider!R&'R1a'&C11)"

End With


With .ChartObjects(3) 'Lipids

.Activate

ActiveChart.PlotArea.Select
' .PlotArea.Select
.SeriesCollection(1).Values = _
"=(mktblTestLipidsMembrProvider!R&'R1a'&C4, mktblTestLipidsMembrProvider!R& 'R1a'&C7, mktblTestLipidsMembrProvider!R&'R1a'&C10)"
.SeriesCollection(2).Values = _
"=(mktblTestLipidsMembrProvider!R&'R1a'&C3, mktblTestLipidsMembrProvider!R& 'R1a'&C6, mktblTestLipidsMembrProvider!R&'R1a'&C9)"
.SeriesCollection(3).Values = _
"=(mktblTestLipidsMembrProvider!R&'R1a'&C5, mktblTestLipidsMembrProvider!R& 'R1a'&C8, mktblTestLipidsMembrProvider!R&'R1a'&C11)"

End With


With .ChartObjects(4) 'Micro

.Activate

ActiveChart.PlotArea.Select
' .PlotArea.Select
.SeriesCollection(1).Values = _
"=(mktblTestMicroMembrProvider!R&'R1a'&C4, mktblTestMicroMembrProvider!R& 'R1a'&C7, mktblTestMicroMembrProvider!R&'R1a'&C10)"
.SeriesCollection(2).Values = _
"=(mktblTestMicroMembrProvider!R&'R1a'&C3, mktblTestMicroMembrProvider!R& 'R1a'&C6, mktblTestMicroMembrProvider!R&'R1a'&C9)"
.SeriesCollection(3).Values = _
"=(mktblTestMicroMembrProvider!R&'R1a'&C5, mktblTestMicroMembrProvider!R& 'R1a'&C8, mktblTestMicroMembrProvider!R&'R1a'&C11)"

End With


End With

R1a = R1a + 1
i = i + 1

Loop


End Sub


I am open to suggestions, and definitely corrections on my WITH statement.

 

[SkipVought]

Hi,

1-
You say...
I have four charts on a worksheet...

and then you say...
(a separate worksheet for each chart).

2-
You have as row, col for your 3 series...
1- (3,4), (3,7), (3,10)
2- (2,3), (2,6), (2,9)
3- (3,5), (3,8), (3,11)

... so are you selecting from rows 2 & 3?

Enquiring minds need to know Skip,
metzgsk@voughtaircraft.com

 

[godfrey62]

sorry for the confusion. I do have four charts on one worksheet. The four charts have four separate worksheets as their SOURCE DATA.

For chart 1, for example, source data is Worksheet("DRE_Info&quot, Row 2, Columns 4, 7 and 10 for first data element in series. For second data element, the source data is the same worksheet, also Row 2, Columns 3, 6, 9, and similar for last data element, only using columns 5, 8, and 11.

so your matrix above would actually be:
1 - (2,4), (2,7), (2,10)
2 - (2,3), (2,6), (2,9)
3 - (2,5), (2,8), (2,11)

Hope that helps enquiring minds. BTW I can move the data such that source for first in series is (2,3), (2,4), (2,5)
and so on.

 

[godfrey62]

sorry for the confusion. I do have four charts on one worksheet. The four charts have four separate worksheets as their SOURCE DATA.

For chart 1, for example, source data is Worksheet("DRE_Info&quot, Row 2, Columns 4, 7 and 10 for first data element in series. For second data element, the source data is the same worksheet, also Row 2, Columns 3, 6, 9, and similar for last data element, only using columns 5, 8, and 11.

so your matrix above would actually be:
1 - (2,4), (2,7), (2,10)
2 - (2,3), (2,6), (2,9)
3 - (2,5), (2,8), (2,11)

Hope that helps enquiring minds. BTW I can move the data such that source for first in series is (2,3), (2,4), (2,5)
and so on.

 

[SkipVought]

Are your charts imbedded in a worksheet or are they Chart sheet objects? Skip,
metzgsk@voughtaircraft.com

 

[godfrey62]

they are embedded in a worksheet. Hence my supposed need to use WITH statements for each iteration.

 

[SkipVought]

OK.

If I am understanding you, then something like this might help.

You may need to adjust how the worksheet and associated chart are coordinated...

Sub DoCharts()
    Dim lRow As Long, lRowCount As Long, iChart As Integer, rng As Range, sName As String
    
    'this sets a range from row 2 col 2 DOWN to the end of your data
    Set rng = Range(Cells(2, 2), Cells(2, 2).End(xlDown))
    With rng
        lRowCount = .Rows.Count     'lRowCount should come out to be 183 at the present time
    End With

    For lRow = 2 To lRowCount + 1
        'for this row, select a chart/worksheet
        For iChart = 1 To 4
            Worksheets(iChart).Activate
            sName = ActiveSheet.Name
            With Worksheet(iChart).ChartObjects(iChart)
                .SeriesCollection(1).Values = "=(" & sName & "!R" & lRow & "C4," & sName & "!R" & lRow & "C7," & sName & "!R" & lRow & "C10)"
                .SeriesCollection(2).Values = "=(" & sName & "!R" & lRow & "C3," & sName & "!R" & lRow & "C6," & sName & "!R" & lRow & "C9)"
                .SeriesCollection(3).Values = "=(" & sName & "!R" & lRow & "C5," & sName & "!R" & lRow & "C8," & sName & "!R" & lRow & "C11)"
            End With
        Next
    Next
End Sub

Let me know if this helps Skip,
metzgsk@voughtaircraft.com

 

[godfrey62]

That looks like the real key to it all, and I've brought it in and fixed my worksheet order, etc., to utilize the code as it's set up . . .

HOWEVER, I now get a Run-time error -438- message that reads: Object doesn't support this property or method. and the line
.SeriesCollection(1).Values = "=(" & sName & "!R" & lRow & "C3," & sName & "!R" & lRow & "C4," & sName & "!R" & lRow & "C5)"

is highlighted for debugging.

I got this message before, so now I'm confused. This is the way Excel created the macro to look, using the SeriesCollection(1).Values = "xxxxx" layout.

Any further thoughts on why I would be getting this error message, and any solutions? What you've done so far has been a life-saver!!!

 

[SkipVought]

You want to be sure that the WORKSHEET is selected at run time and NOT any CHART. Skip,
metzgsk@voughtaircraft.com

 

[godfrey62]

Just so the rest of you know the culmination of the rest of the conversation SkipVought and I had offline, here's the end result. And it works like a charm!!!!!

Option Explicit

Sub DoCharts()

Dim lRow As Long, lRowCount As Long, iChart As Integer, rng As Range, sName As String
Dim wksReport As Worksheet

Set wksReport = ActiveSheet

Worksheets("Sheet1&quot.Activate

'this sets a range from row 2 col 2 DOWN to the end of your data
Set rng = Worksheets("Sheet1&quot.Range(Cells(2, 2), Cells(2, 2).End(xlDown))

With rng
lRowCount = .Rows.Count 'lRowCount should come out to be 183 at the present time
End With

wksReport.Activate

For lRow = 2 To lRowCount + 1
'for this row, select a chart/worksheet

' [a2].Select = "=Sheet1!" & lRow & "A2"""

For iChart = 1 To 4
If iChart = 1 Then
sName = "Sheet1"
ElseIf iChart = 2 Then
sName = "Sheet2"
ElseIf iChart = 3 Then
sName = "Sheet3"
ElseIf iChart = 4 Then
sName = "Sheet4"
End If

With ActiveSheet.ChartObjects(iChart).Chart

.SeriesCollection(1).Values = "=(" & sName & "!R" & lRow & "C3," & sName & "!R" & lRow & "C4," & sName & "!R" & lRow & "C5)"
.SeriesCollection(2).Values = "=(" & sName & "!R" & lRow & "C6," & sName & "!R" & lRow & "C7," & sName & "!R" & lRow & "C8)"
.SeriesCollection(3).Values = "=(" & sName & "!R" & lRow & "C9," & sName & "!R" & lRow & "C10," & sName & "!R" & lRow & "C11)"
End With

'iChart = iChart + 1

Next

'the following to items puts the Company Identifying Number in Cell B3 and the Company Name in Cell C3
wksReport.Cells(3, 2).Value = "=(" & sName & "!R" & lRow & "C1)"
wksReport.Cells(3, 3).Value = "=(" & sName & "!R" & lRow & "C2)"

wksReport.PrintOut
Next

End Sub

Thanks SkipVought for all of your help!! You made this a workable solution for other things we have coming up!!

Heidi

 

[Terence]

What no Star!!! no respect, no respect!