Macros to repeat chart generation

memanuele123 · #1 07-30-2009, 11:26 AM

Hello,
I am new to this site, and unfortunately have NO experience with VBA or programming for that matter (apologies in advance). I am moderately proficient with excel otherwise.

I have a data set with hundreds of rows, each row containing a label in column 1 followed 16 columns of numbers. I would like to generate a simple line graph for each row. Each graph needs to have a 2 lines, one line charting the first 8 numbers of the row and the second line charting the last 8 numbers. Also, ideally the graph will be labeled with with the row name that is in column 1. I have a feeling this is trivial, but with no prior experience I cannot figure it out. I had hoped i could record a macro with "Use relative reference" turned on, and then run the macro from the first column over and over. When i do this, it keeps returning a graph with the initial data set. Thanks in advance for any help or direction.

bhofsetz · #2 07-30-2009, 11:37 AM

Post the macro you recorded and that will help us help you.

Remember to use the code tags # around the code you post.

memanuele123 · #3 07-30-2009, 01:38 PM

Here is what i recorded, i hope i am doing this right. I appreciate you patience.

Code:

Sub Macro21()
'
' Macro21 Macro
'

'
    ActiveCell.Offset(-8, 1).Range("A1:G1").Select
    ActiveSheet.Shapes.AddChart.Select
    ActiveChart.SetSourceData Source:=Range("'Sheet1'!$B$1:$H$1")
    ActiveChart.ChartType = xlLine
    ActiveChart.SeriesCollection.NewSeries
    ActiveChart.SeriesCollection(2).Values = "='Sheet1'!$I$1:$O$1"
    ActiveChart.SeriesCollection(2).Name = "='Sheet1'!$A$1"
End Sub

bhofsetz · #4 07-30-2009, 01:53 PM

Put your code to create the charts inside a loop that will first see how many rows your sheet has then create a new chart for each row. This code puts the chart on the active sheet and each chart is placed in the same place. So when the code finishes running it looks like there is only one chart with the for the last dataset but all the rest are behind the most recently created chart.

Code:

Sub CreateChart()
Dim numRows As Long

numRows = ActiveSheet.Cells(Rows.Count, "A").End(xlUp).Row

For x = 2 To numRows
    ActiveSheet.Shapes.AddChart.Select
    ActiveChart.SetSourceData Source:=Range("'Sheet1'!$B$" & x & ":$H$" & x)
    ActiveChart.ChartType = xlLine
    ActiveChart.SeriesCollection.NewSeries
    ActiveChart.SeriesCollection(2).Values = "='Sheet1'!$I$" & x & ":$O$" & x
    ActiveChart.SeriesCollection(2).Name = "='Sheet1'!$A$" & x
Next x

End Sub

memanuele123 · #5 07-30-2009, 03:15 PM

Thanks worked perfectly, thanks a ton for your help!

memanuele123 · #6 07-30-2009, 04:44 PM

I spoke to soon, one last question.

If i run that macro on more than 15 or so rows of data it does not properly draw the graphs. Is there a way to fix that?

Andy Pope · #7 07-31-2009, 04:47 AM

What's the difference between 15th and 16th row of data and created chart?

Can you post example workbook

memanuele123 · #8 07-31-2009, 01:33 PM

The data is the same between those rows. In fact, if I do this with identical data in every row it still happens. It is technically if i add a 16th row to the table (so it happens between the addition of a 14th and 15th row of data- the top row is column headings).

The difference between the two types of graphs i get back is:
1- the correct, or intended way (which works with under 15 rows): returns a chart with 2 series. Series 1 corresponding to data in columns B-H and series 2 corresponding to columns I-O.
2- unintended, to many rows way: returns 8 series per chart. One series corresponds to columns I through O, and the other 7 series correspond to a single point for columns B through H.

Andy Pope · #9 08-01-2009, 06:45 AM

The problem is caused by Excel trying to guess the data to use in the chart.

If the activecell is within the data table then the whole table is used to populate the chart. Once you have >15 rows of data excel changes the PlotBy from rows to columns. So when you set the data source the row is treated as the category rather than the columns.

This code tweak will correct that.

Code:

Sub CreateChart()
Dim numRows As Long
Dim x

numRows = ActiveSheet.Cells(Rows.Count, "A").End(xlUp).Row

For x = 2 To numRows
    ActiveSheet.Shapes.AddChart.Select
    With ActiveChart
        Do While .SeriesCollection.Count > 1
            .SeriesCollection(1).Delete
        Loop
        .SetSourceData Source:=Range("'Sheet1'!$B$" & x & ":$H$" & x)
        .PlotBy = xlRows
        .ChartType = xlLine
        .SeriesCollection.NewSeries
        .SeriesCollection(2).Values = "='Sheet1'!$I$" & x & ":$O$" & x
        .SeriesCollection(2).Name = "='Sheet1'!$A$" & x
    End With
Next x

End Sub

memanuele123 · #10 08-03-2009, 04:36 PM

That did it! Thanks for all of the help.

Now i just need to get my computer to be able to handle making hundreds of those at once

Welcome to the Excel Forum

Forum Statistics