Multiple Charts

[Joe Mac]

I have a fairly large data set (21000 rows) and i'm trying to write a macro to plot each set of 1000 points. So far my macro goes through once perfectly, but each subsequent graph is the same as the first. I know that the first series starts on row 7 and goes to 1006, the next starts at 1008 and goes to 2007 and so on, but each time I run this there will be a different number of series so I don't want to have it static. I've stepped through the code and this line doesn't update through each iteration.

aaaCell = Cells(aaaRow, aaaColumn).Address

Here is the loop part:

While aaaRow < myLastRow
bbbRow = aaaRow + 999
aaaCell = Cells(aaaRow, aaaColumn).Address
bbbCell = Cells(bbbRow, aaaColumn).Address
myrange = aaaCell & ":" & bbbCell
aaaRow = aaaRow + 1001
Charts.Add
ActiveChart.ChartType = xlXYScatterSmooth
ActiveChart.SetSourceData Source:=Sheets("0070JUL132006@0944").Range( _
myrange)
ActiveChart.Location Where:=xlLocationAsObject, Name:="0070JUL132006@0944"

Wend


What is interesting, is if I comment out just the Charts.Add line, and step through, the cell range updates fine, but when it charts then, it just charts the first one over and over. Is there a probelm with have a Chart.Add in a loop? Whats the best way to do that?

Thanks

Joe Mac

[VBAhack]

Welcome to the forums.

I ran the following small scale prototype of what you are doing and it works ok. There are 12 rows of data (rows 1-12) and 6 columns (A-F). Each plot contains 3 rows of data for a total of 4 charts.

Sub ChartTest()
    Dim aaaRow As Integer, bbbRow As Integer, incr As Integer, myLastRow As Integer
    Dim myRange As String
    incr = 2
    aaaRow = 0
    bbbRow = 0
    myLastRow = 10
    
    While aaaRow < myLastRow
        aaaRow = bbbRow + 1
        bbbRow = aaaRow + incr
        myRange = "A" & aaaRow & ":F" & bbbRow
        Charts.Add
        With ActiveChart
            .ChartType = xlXYScatterSmooth
            .SetSourceData Source:=Sheets("Sheet1").Range(myRange)
            .Location Where:=xlLocationAsObject, Name:="Sheet1"
        End With
    Wend
End Sub

[Joe Mac]

Awesome. So it appears that my cell creation was cr@p trying to use Cells(row, column).Address instead of just "e" & row. Thanks for the help.

God Bless

Joe Mac