Create a chart with a macro

[jamesgw]

I am trying to create a bar chart by recording a macro. My source data is sent from an Access table to a sheet. The number of rows can vary widely. In recording, after I choose the type of chart I want, It asks me for the source data. I am using "CTRL-A" to choose all data. When I finish my chart, and run the macro, all goes according to plan. HOWEVER, when I use NEW data sent over from access, and it has MORE rows than the previous chart had, the macro will only choose the same RANGE OF DATA that I had previously used. When I go in to edit the macro, it show (for example):
ActiveChart.SetSourceData Source:=Sheets("NewChart").Range("A1:C21"), PlotBy _
:=xlColumns
My new data has 36 rows, but the macro has HARDCODED the previous old data rows. What am I doing wrong??? Thanks!!!!

[ This Message was edited by: jamesgw on 2002-05-03 12:02 ]

[kkknie]

When you record a macro, it is always hardcoded. To get around this you must dynamically define your range. Here is how I would do it.

dim iRow as Integer

iRow = Sheets("NewChart").Range("A1").CurrentRegion.Rows.Count

ActiveChart.SetSourceData Source:=Sheets("NewChart").Range(cells(1,1),cells(iRow,3), PlotBy _ :=xlColumns

This assumes that your data will always be in a sheet called "NewChart" and that you have three columns with data starting in cell A1. To make it dynamic for new columns change to:

dim iRow as Integer
dim iCol as Integer

iRow = Sheets("NewChart").Range("A1").CurrentRegion.Rows.Count

iCol = Sheets("NewChart").Range("A1").CurrentRegion.Columns.Count

ActiveChart.SetSourceData Source:=Sheets("NewChart").Range(cells(1,1),cells(iRow,iCol), PlotBy :=xlColumns

Good luck,

K

[jamesgw]

Thanks K, that seems to be a neat solution.


BUT,

When I tried it, I got a totally new error. This time it says:

Run-time error '1004';
Method 'cells' of object '_global' failed.

It stops on the "ActiveChart.SETSOURCE DATA Source" line.

Here is the first part of my macro.

Sub graph()
Dim iRow As Integer

Sheets("graph").Select
ActiveWindow.SelectedSheets.Delete 'delete old graph
Range("B1").Select
Selection.ClearContents 'remove title from 2nd column
Range("C1").Select
ActiveCell.FormulaR1C1 = "Length of Study" 'change 3rd column title
Cells.Select
Range("D2").Activate
iRow = Worksheets("NewChart").Range("A1").CurrentRegion.Rows.Count
Charts.Add 'add chart
ActiveChart.ChartType = xlBarStacked 'create stacked bar graph
ActiveChart.SetSourceData Source:=Sheets("NewChart").Range(Cells(1, 1), Cells(iRow, 3)), PlotBy:=xlColumns
ActiveChart.Location Where:=xlLocationAsNewSheet, Name:="GRAPH" ' change sheet name
With ActiveChart
.HasTitle = True

Could anyone tell me WHAT to look for here?
Thanks!!




[ This Message was edited by: jamesgw on 2002-05-06 09:53 ]

[jamesgw]

[Old Man Winter]

I am having the same problem trying to implement this code. I have another solution to selecting a dynamic area for a chart macro:
Range("A1:F1").Select
Range(Selection, Selection.End(xlDown)).Select

Charts.Add
ActiveChart.ChartType = xlLineMarkers
...

So I get the first 6 columns, as many rows are are present. Works great, except that occasionally it farts and plots data by row instead of by column. So I want to implement the PlotBy:=column, which is (cleverly!) embedded in the ActiveChart.SetSourceData portion of the code, and cannot be seperated. So I am trying to jump through these hoops to specify PlotBy:=Columns. I tried the iRow solution posted by kkknie and am getting the same Method 'cells' of object '_Global' failed error. No idea what it's talking about. Anyone else ever get a solution to this problem?

[striderkap]

I was having the same problem until I saw your post and had the idea to try this

Range(Cells(StartRow, StartColPtr), Cells(EndRow, LastColPtr)).Select
Charts.Add
ActiveChart.ChartType = xlLine
ActiveChart.PlotBy = xlRows

Note that the ActiveChart object takes the PlotBy so that it will force plot method you want.

[riteshnarain]

Set r1 = Range(Cells(1, 1), Cells(RowLimit, columnLimit))
Sheets("CHRT1 - Av Against Due by Date").Select
ActiveChart.PlotArea.Select
ActiveChart.SetSourceData Source:=r1, PlotBy:=xlColumns

I have just posted snippet of my code but you should be able to figure out how to use it in your code

ritesh.narain@gmail.com

Ritesh