Help with VBA code

[Jeffrey Marks]

This is the code I wish to use to create a bar chart and do specific things to it. However, it keeps aborting on "Chart 8" because the next time I use the macro, it becomes "Chart 9."

How would I add VBA code to make the chart number (and row number) increase each time I use the routine?

Thanks

Jeff

Sub OATBarChart()
'
' OATBarChart Macro
'

'
Range("E1:J2").Select
ActiveSheet.Shapes.AddChart.Select
ActiveChart.SetSourceData Source:=Range( _
"'OAT Test Charts Data_Crosstab'!$E$1:$J$2")
ActiveChart.ChartType = xlColumnClustered
ActiveChart.Legend.Select
Selection.Delete
ActiveSheet.ChartObjects("Chart 8").Activate
ActiveChart.HasAxis(xlValue) = True
ActiveSheet.ChartObjects("Chart 8").Activate
ActiveChart.Axes(xlValue).Select
ActiveChart.Axes(xlValue).MinimumScale = 0
ActiveChart.Axes(xlValue).MaximumScale = 1
ActiveChart.Axes(xlValue).MajorUnit = 0.1
ActiveChart.Axes(xlValue).MajorUnit = 0.2
Selection.TickLabels.NumberFormat = "0.00%"
Selection.TickLabels.NumberFormat = "0%"
ActiveSheet.ChartObjects("Chart 8").Activate
ActiveChart.ChartArea.Select
ActiveSheet.ChartObjects("Chart 8").Activate
ActiveChart.SetElement (msoElementPrimaryValueAxisTitleVertical)
ActiveChart.SetElement (msoElementChartTitleAboveChart)
ActiveSheet.ChartObjects("Chart 8").Activate
ActiveChart.ChartTitle.Text = "Adams County - 8th Grade Mathematics Results"
ActiveSheet.ChartObjects("Chart 8").Activate
ActiveChart.ChartArea.Select
ActiveSheet.ChartObjects("Chart 8").Activate
ActiveChart.Axes(xlValue).AxisTitle.Select
ActiveSheet.ChartObjects("Chart 8").Activate
End Sub

 

[Jim Cone]

I set up an object reference to the newly added chart and used that in the code.
That was done by declaring the "chtNew" variable as a chart: Dim chtNew As Chart
and by telling Excel what it referred to: Set chtNew = ActiveChart
'----------
Sub OATBarChart_R1()
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'!$E$1:$J$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"
End Sub
'---------
Jim Cone
Portland, Oregon USA
http://www.mediafire.com/PrimitiveSoftware
(free and commercial excel programs)









"Jeffrey Marks" <[email protected]>
wrote in message

This is the code I wish to use to create a bar chart and do specific things to it.

However, it keeps aborting on "Chart 8" because the next time I use the macro, it becomes "Chart 9."