Hi Jackie,

Will this work instead?

Best regards

John


'Initialize Variables
' Comment out - intLoopCount = 8 'Start in row 8 for loop
intRowStart = 8 'Start in row 8
intRowEnd = 9 'End in row 9
intSheetChartCount = 1 'First chart on sheet
intSheetCount = 1 'First sheet of charts

'Loop to create charts until no data is found
For intLoopCount = intRowStart To (Sheet4.UsedRange.Rows.Count / 4) + 1


"Jackie" wrote in message
...
I have the following code which will not run past the last line. I am
trying to create charts 4 to a page using every 8th and 9th row of data.
Any
help is appreciated.

Declare Variables
Dim intLoopCount As Integer 'Counter for loop
Dim intRowStart As Integer 'Row number to start at for first chart
Dim intRowEnd As Integer 'Row number to end at for first chart
Dim intSheetChartCount As Integer 'Count of charts on a sheet
Dim intSheetCount As Integer 'Count of sheets
Dim strSheetName As String 'Name of sheet

'Initialize Variables
intLoopCount = 8 'Start in row 8 for loop
intRowStart = 8 'Start in row 8
intRowEnd = 9 'End in row 9
intSheetChartCount = 1 'First chart on sheet
intSheetCount = 1 'First sheet of charts

'Loop to create charts until no data is found
For intLoopCount = intLoopCount To (Sheet4.UsedRange.Rows.Count / 4) + 1

John's (Bundy) right. It's the problem with the Worksheet object.

This works for me:

For intLoopCount = intRowStart To (Sheets(4).UsedRange.Rows.Count / 4) + 1

This refers to a worksheet index rather than its name as John's string was
doing. Note that as earlier, I've also changed to the beginning loop
counter to intRowStart, otherwise you are effectively saying Let
intLoopCount (which you earlier set to a value of 8) = itself, but this
value will change and I'm not sure how that will behave in your code.

Anyway, hope this gets you going?

All the best

John (a different one!)

"Jackie" wrote in message
...
No, sorry, that didn't work. Is there any other information I can give
you
that might be helpful?

"John Bundy" wrote:

Hard to tell without being able to test but so far, that bottom line you
errored on

For intLoopCount = intLoopCount To (Sheet4.UsedRange.Rows.Count / 4) + 1

Try this

For intLoopCount = intLoopCount To (Sheets("Sheet4").UsedRange.Rows.Count
/
4) + 1

--
-John
Please rate when your question is answered to help us and others know
what
is helpful.


"Jackie" wrote:

Thanks for taking time to look at this, I am sure I gave you too much
information, but maybe you can see where I am going wrong. Thanks
again. J-

'Declare Variables
Dim intLoopCount As Integer 'Counter for loop
Dim intRowStart As Integer 'Row number to start at for first chart
Dim intRowEnd As Integer 'Row number to end at for first chart
Dim intSheetChartCount As Integer 'Count of charts on a sheet
Dim intSheetCount As Integer 'Count of sheets
Dim strSheetName As String 'Name of sheet

'Initialize Variables
'Comment out -intLoopCount = 8 'Start in row 8 for loop
intRowStart = 8 'Start in row 8
intRowEnd = 9 'End in row 9
intSheetChartCount = 1 'First chart on sheet
intSheetCount = 1 'First sheet of charts

'Loop to create charts until no data is found
For intLoopCount = intLoopCount To (Sheet4.UsedRange.Rows.Count / 4) +
1


'Check if new sheet needs to be created
If intSheetChartCount = 1 Then

'Create a blank sheet
Worksheets.Add.Name = "Sheet" & intSheetCount

'Name Sheet
strSheetName = ("'GradesExamscharts'!c" & intRowStart & "C8:R"
&
intRowEnd & "R9")

'Increment counter
intSheetCount = intSheetCount + 1

End If

'Create a blank chart
Charts.Add

'Format chart as Cluster Column
ActiveChart.ChartType = xlColumnClustered

'Set data source and data range
ActiveChart.SetSourceData
Source:=Sheets("GradesExamscharts").Range( _
"A" & intRowStart & ":D" & intRowEnd & _
",F" & intRowStart & ":F" & intRowEnd & _
",H" & intRowStart & ":H" & intRowEnd & _
",J" & intRowStart & ":J" & intRowEnd & _
",L" & intRowStart & ":L" & intRowEnd & _
",N" & intRowStart & ":N" & intRowEnd), PlotBy:=xlRows

ActiveChart.SeriesCollection(1).XValues = _
"=('GradesExamscharts'!c" & intRowStart & "C8:R" & intRowEnd &
"R9"
ActiveChart.SeriesCollection(1).Name = "=""Exam"""
ActiveChart.SeriesCollection(2).XValues = _
"=('GradesExamscharts'!c" & intRowStart & "C8:R" & intRowEnd &
"R9"
ActiveChart.SeriesCollection(2).Name = "=""Crs Gr"""

ActiveChart.Location Whe=xlLocationAsObject, Name:=strSheetName


ActiveChart.HasLegend = False
ActiveChart.HasDataTable = True
ActiveChart.DataTable.ShowLegendKey = True
ActiveChart.ChartTitle.Select

Selection.Text = "='GradesExamscharts'!c" & intRowStart & "C8:R" &
intRowEnd & "R9"

ActiveChart.Axes(xlValue).Select
ActiveChart.SeriesCollection(2).Select

ActiveChart.HasLegend = True
ActiveChart.Legend.Select
Selection.Position = xlLeft
ActiveChart.Legend.Select
Selection.Left = 7
Selection.Top = 398
ActiveChart.ChartArea.Select
Selection.AutoScaleFont = True

With Selection.Font
.Name = "Arial"
.Size = 7
.Strikethrough = False
.Superscript = False
.Subscript = False
.OutlineFont = False
.Shadow = False
.Underline = xlUnderlineStyleNone
.ColorIndex = xlAutomatic
.Background = xlAutomatic
End With
With ActiveChart
.HasTitle = True
.ChartTitle.Characters.Text = "='GradesExamscharts'!c" &
intRowStart
& "R8:C" & intRowStart & "c8"

End With
Selection.AutoScaleFont = True
With Selection.Font
.Name = "Arial"
.Size = 10
.Strikethrough = False
.Superscript = False
.Subscript = False
.OutlineFont = False
.Shadow = False
.Underline = xlUnderlineStyleNone
.ColorIndex = xlAutomatic
.Background = xlAutomatic

End With

With Selection.TickLabels.Font
.Name = "Arial"
.FontStyle = "Regular"
.Size = 7
.Strikethrough = False
.Superscript = False
.Subscript = False
.OutlineFont = False
.Shadow = False
.Underline = xlUnderlineStyleNone
.ColorIndex = xlAutomatic
.Background = xlAutomatic
End With

ActiveChart.Axes(xlValue).MajorGridlines.Select
ActiveChart.ChartArea.Select
ActiveChart.PlotArea.Select
Selection.Top = 21
Selection.Height = 252
ActiveChart.Axes(xlValue).Select

With ActiveChart.Axes(xlValue)
.MinimumScaleIsAuto = True
.MaximumScaleIsAuto = True
.MinorUnit = 4
.MajorUnit = 20
.Crosses = xlAutomatic
.ReversePlotOrder = False
.ScaleType = xlLinear
.DisplayUnit = xlNone
End With

'Increment counters
intRowStart = intRowStart + 8
intRowEnd = intRowEnd + 8
intSheetChartCount = intSheetChartCount + 1

'Reset sheet chart counter to 1 if it goes beyond 4
If intSheetChartCount = 5 Then

intSheetChartCount = 1

End If

Next intLoopCount

End Sub

"John Bundy" wrote:

Try posting as much code as you can, we will have to reproduce the
error to
troubleshoot. Microsoft is vague to say the least when it comes to
errors.
--
-John
Please rate when your question is answered to help us and others know
what
is helpful.


"Jackie" wrote:

Thanks John,
No, that did not work, still returns the run-time error 424 object
required.

"John" wrote:

Hi Jackie,

Will this work instead?

Best regards

John


'Initialize Variables
' Comment out - intLoopCount = 8 'Start in row 8 for loop
intRowStart = 8 'Start in row 8
intRowEnd = 9 'End in row 9
intSheetChartCount = 1 'First chart on sheet
intSheetCount = 1 'First sheet of charts

'Loop to create charts until no data is found
For intLoopCount = intRowStart To (Sheet4.UsedRange.Rows.Count /
4) + 1


"Jackie" wrote in message
...
I have the following code which will not run past the last line.
I am
trying to create charts 4 to a page using every 8th and 9th row
of data.
Any
help is appreciated.

Declare Variables
Dim intLoopCount As Integer 'Counter for loop
Dim intRowStart As Integer 'Row number to start at for first
chart
Dim intRowEnd As Integer 'Row number to end at for first chart
Dim intSheetChartCount As Integer 'Count of charts on a sheet
Dim intSheetCount As Integer 'Count of sheets
Dim strSheetName As String 'Name of sheet

'Initialize Variables
intLoopCount = 8 'Start in row 8 for loop
intRowStart = 8 'Start in row 8
intRowEnd = 9 'End in row 9
intSheetChartCount = 1 'First chart on sheet
intSheetCount = 1 'First sheet of charts

'Loop to create charts until no data is found
For intLoopCount = intLoopCount To (Sheet4.UsedRange.Rows.Count
/ 4) + 1

That's fine. So we've solved the object reference problem and now we can
move on to the next.

The next one (subscript out of range) looks like its try to reference
something that's not there. So, for example Sheets(4) will produce this
error if there are only 3 worksheets.

Assuming that your new error occured on the same line, then I assume you
don't have four worksheets in the workbook. Perhaps you could spell out the
logic of what you are trying to achieve. ie. Take a workbook, add a chart
to the last worksheet, do stuff to the new chart etc. Of course if its not
that line that's cause the new error then you'll need to look elsewhere.

Two useful tips for trying to diagnose problems a

a) Step through the code line by line using the F8 key in the VBE (code
window). you can then hover the mouse over a variable and see what its
value is at that moment.

b) Add Debug statements in your code such as "Debug.Print
Sheets(4).UsedRange.Rows.Count" or "Debug.Print Sheets.Count". This will
show the result of the value at that point in the "Immediate window" (Click
View/Immediate Window from the menubar).

Let me know what you find.

Best regards

John


"Jackie" wrote in message
...
I so appreciate all the help, but now the error (on the same line) is
Run time error 9 - Subscript out of range. Would you mind taking one more
look? Thanks. J-
"John" wrote:

John's (Bundy) right. It's the problem with the Worksheet object.

This works for me:

For intLoopCount = intRowStart To (Sheets(4).UsedRange.Rows.Count / 4)
+ 1

This refers to a worksheet index rather than its name as John's string
was
doing. Note that as earlier, I've also changed to the beginning loop
counter to intRowStart, otherwise you are effectively saying Let
intLoopCount (which you earlier set to a value of 8) = itself, but this
value will change and I'm not sure how that will behave in your code.

Anyway, hope this gets you going?

All the best

John (a different one!)

"Jackie" wrote in message
...
No, sorry, that didn't work. Is there any other information I can give
you
that might be helpful?

"John Bundy" wrote:

Hard to tell without being able to test but so far, that bottom line
you
errored on

For intLoopCount = intLoopCount To (Sheet4.UsedRange.Rows.Count / 4) +
1

Try this

For intLoopCount = intLoopCount To
(Sheets("Sheet4").UsedRange.Rows.Count
/
4) + 1

--
-John
Please rate when your question is answered to help us and others know
what
is helpful.


"Jackie" wrote:

Thanks for taking time to look at this, I am sure I gave you too
much
information, but maybe you can see where I am going wrong. Thanks
again. J-

'Declare Variables
Dim intLoopCount As Integer 'Counter for loop
Dim intRowStart As Integer 'Row number to start at for first chart
Dim intRowEnd As Integer 'Row number to end at for first chart
Dim intSheetChartCount As Integer 'Count of charts on a sheet
Dim intSheetCount As Integer 'Count of sheets
Dim strSheetName As String 'Name of sheet

'Initialize Variables
'Comment out -intLoopCount = 8 'Start in row 8 for loop
intRowStart = 8 'Start in row 8
intRowEnd = 9 'End in row 9
intSheetChartCount = 1 'First chart on sheet
intSheetCount = 1 'First sheet of charts

'Loop to create charts until no data is found
For intLoopCount = intLoopCount To (Sheet4.UsedRange.Rows.Count / 4)
+
1


'Check if new sheet needs to be created
If intSheetChartCount = 1 Then

'Create a blank sheet
Worksheets.Add.Name = "Sheet" & intSheetCount

'Name Sheet
strSheetName = ("'GradesExamscharts'!c" & intRowStart &
"C8:R"
&
intRowEnd & "R9")

'Increment counter
intSheetCount = intSheetCount + 1

End If

'Create a blank chart
Charts.Add

'Format chart as Cluster Column
ActiveChart.ChartType = xlColumnClustered

'Set data source and data range
ActiveChart.SetSourceData
Source:=Sheets("GradesExamscharts").Range( _
"A" & intRowStart & ":D" & intRowEnd & _
",F" & intRowStart & ":F" & intRowEnd & _
",H" & intRowStart & ":H" & intRowEnd & _
",J" & intRowStart & ":J" & intRowEnd & _
",L" & intRowStart & ":L" & intRowEnd & _
",N" & intRowStart & ":N" & intRowEnd), PlotBy:=xlRows

ActiveChart.SeriesCollection(1).XValues = _
"=('GradesExamscharts'!c" & intRowStart & "C8:R" & intRowEnd
&
"R9"
ActiveChart.SeriesCollection(1).Name = "=""Exam"""
ActiveChart.SeriesCollection(2).XValues = _
"=('GradesExamscharts'!c" & intRowStart & "C8:R" & intRowEnd
&
"R9"
ActiveChart.SeriesCollection(2).Name = "=""Crs Gr"""

ActiveChart.Location Whe=xlLocationAsObject,
Name:=strSheetName


ActiveChart.HasLegend = False
ActiveChart.HasDataTable = True
ActiveChart.DataTable.ShowLegendKey = True
ActiveChart.ChartTitle.Select

Selection.Text = "='GradesExamscharts'!c" & intRowStart & "C8:R"
&
intRowEnd & "R9"

ActiveChart.Axes(xlValue).Select
ActiveChart.SeriesCollection(2).Select

ActiveChart.HasLegend = True
ActiveChart.Legend.Select
Selection.Position = xlLeft
ActiveChart.Legend.Select
Selection.Left = 7
Selection.Top = 398
ActiveChart.ChartArea.Select
Selection.AutoScaleFont = True

With Selection.Font
.Name = "Arial"
.Size = 7
.Strikethrough = False
.Superscript = False
.Subscript = False
.OutlineFont = False
.Shadow = False
.Underline = xlUnderlineStyleNone
.ColorIndex = xlAutomatic
.Background = xlAutomatic
End With
With ActiveChart
.HasTitle = True
.ChartTitle.Characters.Text = "='GradesExamscharts'!c" &
intRowStart
& "R8:C" & intRowStart & "c8"

End With
Selection.AutoScaleFont = True
With Selection.Font
.Name = "Arial"
.Size = 10
.Strikethrough = False
.Superscript = False
.Subscript = False
.OutlineFont = False
.Shadow = False
.Underline = xlUnderlineStyleNone
.ColorIndex = xlAutomatic
.Background = xlAutomatic

End With

With Selection.TickLabels.Font
.Name = "Arial"
.FontStyle = "Regular"
.Size = 7
.Strikethrough = False
.Superscript = False
.Subscript = False
.OutlineFont = False
.Shadow = False
.Underline = xlUnderlineStyleNone
.ColorIndex = xlAutomatic
.Background = xlAutomatic
End With

ActiveChart.Axes(xlValue).MajorGridlines.Select
ActiveChart.ChartArea.Select
ActiveChart.PlotArea.Select
Selection.Top = 21
Selection.Height = 252
ActiveChart.Axes(xlValue).Select

With ActiveChart.Axes(xlValue)
.MinimumScaleIsAuto = True
.MaximumScaleIsAuto = True
.MinorUnit = 4
.MajorUnit = 20
.Crosses = xlAutomatic
.ReversePlotOrder = False
.ScaleType = xlLinear
.DisplayUnit = xlNone
End With

'Increment counters
intRowStart = intRowStart + 8
intRowEnd = intRowEnd + 8
intSheetChartCount = intSheetChartCount + 1

'Reset sheet chart counter to 1 if it goes beyond 4
If intSheetChartCount = 5 Then

intSheetChartCount = 1

End If

Next intLoopCount

End Sub

"John Bundy" wrote:

Try posting as much code as you can, we will have to reproduce the
error to
troubleshoot. Microsoft is vague to say the least when it comes to
errors.
--
-John
Please rate when your question is answered to help us and others
know
what
is helpful.


"Jackie" wrote:

Thanks John,
No, that did not work, still returns the run-time error 424
object
required.

"John" wrote:

Hi Jackie,

Will this work instead?

Best regards

John


'Initialize Variables
' Comment out - intLoopCount = 8 'Start in row 8 for loop
intRowStart = 8 'Start in row 8
intRowEnd = 9 'End in row 9
intSheetChartCount = 1 'First chart on sheet
intSheetCount = 1 'First sheet of charts

'Loop to create charts until no data is found
For intLoopCount = intRowStart To (Sheet4.UsedRange.Rows.Count
/
4) + 1


"Jackie" wrote in message
...
I have the following code which will not run past the last
line.
I am
trying to create charts 4 to a page using every 8th and 9th
row
of data.
Any
help is appreciated.

Declare Variables
Dim intLoopCount As Integer 'Counter for loop
Dim intRowStart As Integer 'Row number to start at for first
chart
Dim intRowEnd As Integer 'Row number to end at for first
chart
Dim intSheetChartCount As Integer 'Count of charts on a
sheet
Dim intSheetCount As Integer 'Count of sheets
Dim strSheetName As String 'Name of sheet

'Initialize Variables
intLoopCount = 8 'Start in row 8 for loop
intRowStart = 8 'Start in row 8
intRowEnd = 9 'End in row 9
intSheetChartCount = 1 'First chart on sheet
intSheetCount = 1 'First sheet of charts

'Loop to create charts until no data is found
For intLoopCount = intLoopCount To
(Sheet4.UsedRange.Rows.Count
/ 4) + 1