CODEWITHSUNDEEP
excelvbacharts
Brian
Brian

Reputation: 549

Excel 2007 Create a number of charts with VBA

I have been trying to create a number of charts using VBA. I used the Macro record function to get started but have not progressed much.

I have a variable data set for which I need to chart each row. One cell is the series name, the data is a (fixed) range of cells on the same row and plotted against a series of dates (top row).

So what I have is this (for a single chart)

Charts.Add
ActiveChart.ChartType = xlColumnClustered
ActiveChart.SetSourceData Source:=Sheets("Data").Range("A3")
ActiveChart.SeriesCollection(1).Name = "='Data'!$A$3"
ActiveChart.SeriesCollection(1).Values = "='Data'!$MO$3:$RY$3"
ActiveChart.SeriesCollection(1).XValues = "='Data'!$MO$1:$RY$1"
ActiveChart.Name = Sheets("Data").Range("A3")

If A3 is the first cell with the name data in, I want to loop round each row generating a new chart for each series.

I have managed to get the start point in from an input box, but that is not really necessary. It is for me to use not for others. I'd be happy if it started at a fixed point and cycled through until it reaches a blank cell.

The bit I cannot do, is work out how to generate the cell ranges for each iteration.

screenshot of data

screenshot of data

Screenshot of example Chart

Screenshot of example Chart

Upvotes: 1

Views: 127

Answers (1)

Carrosive
Carrosive

Reputation: 899

Where a range is referenced you are able to use a variable to 'move' the reference on each iteration. Take for example you want to insert some text into the first 10 Rows of Column A, you could write:

Range("A1").Value = "Hello"
Range("A2").Value = "Hello"
Range("A3").Value = "Hello"
Range("A4").Value = "Hello"
etc...

Instead you could use a loop which 'moves' the Range reference down a row on each iteration by using the loop variable i:

For i = 1 to 10
    Range("A" & i).Value = "Hello"
Next i

You can also adjust the reference in line if required to achieve a desired result, for example Range("A" & i * 2).Value = "Hello" will add text to the even rows 2-20 by multiplying the value of i by 2.

Where you want to repeat the loop for each row you have in your data, you can find the row number of the bottom row using Range("").End(xlDown).Row whilst putting the top cell reference in the "" marks , in your case "A3"

Putting all this together, you're looking for something along the lines of:

for i = 3 to Range("A3").End(xlDown).Row   'Loop for each row from 3 to the bottom
    Charts.Add
    ActiveChart.ChartType = xlColumnClustered
    ActiveChart.SetSourceData Source:=Sheets("Data").Range("A" & i)
    ActiveChart.SeriesCollection(1).Name = "='Data'!$A" & i
    ActiveChart.SeriesCollection(1).Values = "='Data'!$MO" & i & ":$RY" & i
    ActiveChart.SeriesCollection(1).XValues = "='Data'!$MO$1:$RY$1"
    ActiveChart.Name = Sheets("Data").Range("A" & i)
next i

You will probably need to tweak the above if it doesn't work exactly how you expected, but I hope this helps you to figure it out.

Upvotes: 0

Related Questions