For Next help
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
|