Help with VBA code
 

Jim

Thank you! This works great. I have to do this for 100+ charts and hoped to create 1 macro that does this for each row. Row 1 has the labels in the previous example and Row 2 has the data. I would need a counter to increase by 1 (and not include Row 2 in Row 3's chart, etc.) In preparation for this, I also put the ChartTitle in Column D

I've been trying to research a way to do this for bar charts on-line, but haven't found anything applicable. I know I need a loop and a counter, but not sure of where to go to find an example of cluster columns.

Jeff


Sub OATChartCreate()

Dim chtNew As Chart '<<<
ActiveCell.Resize(2.6).Select
ActiveSheet.Shapes.AddChart.Select
Set chtNew = ActiveChart '<<<
chtNew.SetSourceData Source:=Range("'OAT Test Charts Data_Crosstab'!$F$1:$K$2")
chtNew.ChartType = xlColumnClustered
chtNew.Legend.Delete
chtNew.HasAxis(xlValue) = True
chtNew.Axes(xlValue).MinimumScale = 0
chtNew.Axes(xlValue).MaximumScale = 1
chtNew.Axes(xlValue).MajorUnit = 0.1
chtNew.Axes(xlValue).MajorUnit = 0.2
chtNew.Axes(xlValue).TickLabels.NumberFormat = "0.00%"
chtNew.Axes(xlValue).TickLabels.NumberFormat = "0%"
ActiveChart.ChartArea.Select
chtNew.SetElement (msoElementPrimaryValueAxisTitleVertical)
chtNew.SetElement (msoElementChartTitleAboveChart)
chtNew.ChartTitle.Text = "Adams County - 8th Grade Mathematics Results"
chtNew.Axes(xlValue, xlPrimary).AxisTitle.Text = "Percent Passing"
End Sub

Help with VBA code
 

You sound like a candidate for "Sparklines" - a new feature in XL2010.
Some examples shown here...
http://blogs.office.com/b/microsoft-...-in-excel.aspx
'---
Also,
Have you looked in Excel VBA help for "AddChart" found in this line in the code:
"ActiveSheet.Shapes.AddChart.Select"
Note: In VBA, a dot "." is used to join distinct parts of the code.
'---
Jim Cone
Portland, Oregon USA
http://www.mediafire.com/PrimitiveSoftware
(Formats & Styles xl add-in: lists/removes unused styles & number formats - in the free folder)



"Jeffrey Marks"
wrote in message
...
Jim

Thank you! This works great. I have to do this for 100+ charts and hoped to create 1 macro that does
this for each row. Row 1 has the labels in the previous example and Row 2 has the data. I would need
a counter to increase by 1 (and not include Row 2 in Row 3's chart, etc.) In preparation for this, I
also put the ChartTitle in Column D

I've been trying to research a way to do this for bar charts on-line, but haven't found anything
applicable. I know I need a loop and a counter, but not sure of where to go to find an example of
cluster columns.

Jeff


Sub OATChartCreate()

Dim chtNew As Chart '<<<
ActiveCell.Resize(2.6).Select
ActiveSheet.Shapes.AddChart.Select
Set chtNew = ActiveChart '<<<
chtNew.SetSourceData Source:=Range("'OAT Test Charts Data_Crosstab'!$F$1:$K$2")
chtNew.ChartType = xlColumnClustered
chtNew.Legend.Delete
chtNew.HasAxis(xlValue) = True
chtNew.Axes(xlValue).MinimumScale = 0
chtNew.Axes(xlValue).MaximumScale = 1
chtNew.Axes(xlValue).MajorUnit = 0.1
chtNew.Axes(xlValue).MajorUnit = 0.2
chtNew.Axes(xlValue).TickLabels.NumberFormat = "0.00%"
chtNew.Axes(xlValue).TickLabels.NumberFormat = "0%"
ActiveChart.ChartArea.Select
chtNew.SetElement (msoElementPrimaryValueAxisTitleVertical)
chtNew.SetElement (msoElementChartTitleAboveChart)
chtNew.ChartTitle.Text = "Adams County - 8th Grade Mathematics Results"
chtNew.Axes(xlValue, xlPrimary).AxisTitle.Text = "Percent Passing"
End Sub

Help with VBA code
 

On Jul 12, 8:43*am, Jeffrey Marks wrote:
Jim

Thank you! This works great. I have to do this for 100+ charts and hoped to create 1 macro that does this for each row. Row 1 has the labels in the previous example and Row 2 has the data. I would need a counter to increase by 1 (and not include Row 2 in Row 3's chart, etc.) In preparation for this, I also put the ChartTitle in Column D

*I've been trying to research a way to do this for bar charts on-line, but haven't found anything applicable. I know I need a loop and a counter, but not sure of where to go to find an example of cluster columns.

Jeff

Sub OATChartCreate()

*Dim chtNew As Chart * * * * * * * '<<<
* * ActiveCell.Resize(2.6).Select
* * ActiveSheet.Shapes.AddChart.Select
* * Set chtNew = ActiveChart * * '<<<
* * chtNew.SetSourceData Source:=Range("'OAT Test Charts Data_Crosstab'!$F$1:$K$2")
* * chtNew.ChartType = xlColumnClustered
* * chtNew.Legend.Delete
* * chtNew.HasAxis(xlValue) = True
* * chtNew.Axes(xlValue).MinimumScale = 0
* * chtNew.Axes(xlValue).MaximumScale = 1
* * chtNew.Axes(xlValue).MajorUnit = 0.1
* * chtNew.Axes(xlValue).MajorUnit = 0.2
* * chtNew.Axes(xlValue).TickLabels.NumberFormat = "0.00%"
* * chtNew.Axes(xlValue).TickLabels.NumberFormat = "0%"
* * ActiveChart.ChartArea.Select
* * chtNew.SetElement (msoElementPrimaryValueAxisTitleVertical)
* * chtNew.SetElement (msoElementChartTitleAboveChart)
* * chtNew.ChartTitle.Text = "Adams County - 8th Grade Mathematics Results"
* * chtNew.Axes(xlValue, xlPrimary).AxisTitle.Text = "Percent Passing"
End Sub

Take a look at the Chart Assistant, http://www.add-ins.com/chart_assistant.htm.
It is designed to create one chart per row of data and allows you to
customize the charts.

Robert Flanagan
http://www.add-ins.com
Productivity add-ins and downloadable books on VB macros for Excel