Problem with VBA code and Range

[Jeffrey Marks]

I'm trying to create multiple charts via a macro in Excel 2007.
I'm getting a 1004 - Range of Object _Global Failed on the indicated statement. I believe I've done something wrong with the last Range in that statement, but I'm not sure what.


Sub OATChartCreate()

Dim chtNew As Chart
Dim i As Integer '<<<
ActiveCell.Resize(2.6).Select
ActiveSheet.Shapes.AddChart.Select
Set chtNew = ActiveChart
For i = 1 To 109 '<<<
Range("F1:K1,F2:K2").Select
Range("F2").Activate
ActiveCell.Resize(2.6).Select
ActiveSheet.Shapes.AddChart.Select
Set chtNew = ActiveChart"'OAT Test Charts Data_Crosstab'!$F$1:$K$1, Range(ActiveCell,ActiveCell.Offset(0,6)).Select" _
) <<<
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"
Range("F2").Offset(i, 0).Select
Next i
End Sub

Thanks

Jeff

 

[joeu2004]

I'm getting a 1004 - Range of Object _Global Failed
on the indicated statement. [....]"'OAT Test Charts Data_Crosstab'!$F$1:$K$1,
Range(ActiveCell,ActiveCell.Offset(0,6)).Select" _
) <<<

Syntactically, it should be:

chtNew.SetSourceData Source:=Range( _
"'OAT Test Charts Data_Crosstab'!$F$1:$K$1",
Range(ActiveCell,ActiveCell.Offset(0,6)).Select)

Note the change in where the right double-quote is.

PS: I would also eliminate .Select in the second Range parameter. I don't
know if it hurts to have it, but it is not likely that it is helpful, much
less necessary.

 

[joeu2004]

Errata....

Syntactically, it should be:

chtNew.SetSourceData Source:=Range( _
"'OAT Test Charts Data_Crosstab'!$F$1:$K$1",
Range(ActiveCell,ActiveCell.Offset(0,6)).Select)

Actually, even the latter form seems strange. If ActiveCell is not in he
'OAT Test Charts Data_Crosstab' worksheet, the specified range is erroneous.
On the other hand, if ActiveCell is in the 'OAT Test Charts Data_Crosstab'
worksheet, why use that qualifier for F1:K1?

Moreover, specifying Range(range1,range2) is dubious because that selects
only the upper-left cell of range1 and the lower-right cell of range2. So
your statement is equivalent to:

chtNew.SetSourceData Source:=Range("F1",ActiveCell.Offset(0,6))

But I wonder if even that makes good sense. Depends on your intent.

PS: I finally did determine that .Select in that context causes a VBA
error.