Problem with VBA code and Range
 

Thanks for the reply.

My intent is to use F1:K1 as the data that will appear along the x-axis (specifically years 2005, 2006, 2007, etc.) The Range that I was trying to get by using the offset is the row of data (percentages for each year listed in the first row). So I was really looking for F2:K2, then F3:K3, and so on. Each of these would get their own Clustered Column chart. I can't use dynamic tables as they want these charts to cut/paste into several PowerPoints and Word documents for a presentation next month.

Hope that helps and thanks for any help you can give.

Jeff

Problem with VBA code and Range
 

"Jeffrey Marks" wrote:
My intent is to use F1:K1 as the data that will
appear along the x-axis (specifically years 2005,
2006, 2007, etc.) The Range that I was trying to
get by using the offset is the row of data
(percentages for each year listed in the first row).
So I was really looking for F2:K2, then F3:K3, and
so on. Each of these would get their own Clustered
Column chart.

Okay, I see what you are trying to do now. Try the following macro.

See the .SetSourceData and .SeriesCollection(1).XValues for critical fixes.
Also some other minor improvements.

Be sure to read comments, especially NOTE, CAVEAT, CUSTOMIZE and DANGEROUS
comments.


-----

Option Explicit

Sub OATChartCreate()

Dim i As Integer, shName As String
Dim rowData As Range, rowXAxis As Range

' *** CUSTOMIZE ***
shName = "OAT Test Charts Dtat_Crosstab"
Set rowData = Sheets(shName).Range("f2:k2")
Set rowXAxis = Sheets(shName).Range("f1:k1")

Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual

' *** DANGEROUS *** do you want to do this?
' delete all pre-existing chart sheets (clean start)
Application.DisplayAlerts = False
ActiveWorkbook.Charts.Delete
Application.DisplayAlerts = True

' *** CAVEAT ***
' some changes might be needed in references
' to object members.
'
' this is written and tested for XL2003.
' I notice some differences in object member
' names compared to yours. not sure which are
' intentional due to differences between your
' Excel version and XL2003.

' *** NOTE ***
' 0 to 108 instead of 1 to 109.
' makes it easier to use Offset
For i = 0 To 108
Charts.Add
With ActiveChart
.ChartType = xlColumnClustered
.SetSourceData Source:=rowData.Offset(i, 0), _
PlotBy:=xlRows
.SeriesCollection(1).XValues = rowXAxis
.HasLegend = False
.HasTitle = True
.ChartTitle.Characters.Text = _
"Adams County - 8th Grade Mathematics Results"
.Axes(xlValue, xlPrimary).HasTitle = True
.Axes(xlValue, xlPrimary).AxisTitle.Characters.Text = _
"Percent Passing"

With .Axes(xlValue)
.MinimumScale = 0
.MaximumScale = 1
.MinorUnit = 0.1
.MajorUnit = 0.5
.TickLabels.NumberFormat = "0%"
End With

' *** NOTE ***
' I cannot test the following.
' Change #If 0 to #If 1.
' If it works for you, remove #If and #End
' directives
#If 0 Then
.SetElement (msoElementPrimaryValueAxisTitleVertical)
.SetElement (msoElementChartTitleAboveChart)
#End If
End With
ActiveChart.Deselect
Next i

Application.Calculation = xlCalculationAutomatic
Application.ScreenUpdating = True

' aesthetic: leave selection cursor in "nice" place
Sheets(shName).Activate
Range("a1").Activate

End Sub