Help with For - Next Loop

J

Jackie

I am trying to create a For - Next Loop to create a chart for every 7th and
8th row of data. Four charts to a sheet. The data starts in row 2. I am
modifying a macro created by someone else and don't know the first thing
about programming. I am not sure where all I need to make the changes. But
it hangs up after the line:

For intLoopCount = intLoopCount To (Sheet4.UsedRange.Rows.Count / 6) + 1
So this is my first hurdle.

'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 / 6) + 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 = "Sheet" & intSheetCount

'Increment counter
intSheetCount = intSheetCount + 1

End If

'Create a blank chart
Charts.Add

'Format chart as Clustered Column
ActiveChart.ChartType = xlColumnClustered

'Set data source and data range

ActiveChart.SeriesCollection(1).XValues = _
"='GradesExamscharts'!R" & intRowStart & "C1:R" & intRowEnd & "C1"
ActiveChart.SeriesCollection(1).Name = "=""Exams"""
ActiveChart.SeriesCollection(2).XValues = _
"='GradesExamscharts'!R" & intRowStart & "C1:R" & intRowEnd & "C1"
ActiveChart.SeriesCollection(2).Name = "=""Crs Gr"""


ActiveChart.Location Where:=xlLocationAsObject, Name:=strSheetName

ActiveChart.SetSourceData Source:=Sheets("GradesExamscharts").Range( _
"F8:F9,H8:H9,J8:J9,L8:L9,N8:N9"), PlotBy:=xlRows
ActiveChart.SeriesCollection(1).XValues = _

"=(GradesExamscharts!R1C6,GradesExamscharts!R1C8,GradesExamscharts!R1C10,GradesExamscharts!R1C12,GradesExamscharts!R1C14)"
ActiveChart.SeriesCollection(1).Name = "=GradesExamscharts!R8C4"
ActiveChart.SeriesCollection(2).XValues = _

"=(GradesExamscharts!R1C6,GradesExamscharts!R1C8,GradesExamscharts!R1C10,GradesExamscharts!R1C12,GradesExamscharts!R1C14)"
ActiveChart.SeriesCollection(2).Name = "=GradesExamscharts!R9C4"
ActiveChart.Location Where:=xlLocationAsNewSheet
With ActiveChart
.HasTitle = True
.ChartTitle.Characters.Text = "Course"
.Axes(xlCategory, xlPrimary).HasTitle = False
.Axes(xlValue, xlPrimary).HasTitle = False
End With
ActiveChart.HasLegend = True
ActiveChart.Legend.Select
Selection.Position = xlLeft
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
ActiveChart.ChartTitle.Select
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
Selection.Font.Bold = True
ActiveChart.Axes(xlValue).Select

With ActiveChart.Axes(xlValue)
.MinimumScaleIsAuto = True
.MaximumScale = 1
.MinorUnit = 0.04
.MajorUnit = 0.2
.Crosses = xlAutomatic
.ReversePlotOrder = False
.ScaleType = xlLinear
.DisplayUnit = xlNone
End With
ActiveChart.Legend.Select
Selection.Left = 15
Selection.Top = 402
ActiveChart.ChartTitle.Select
Selection.Left = 317
Selection.Text = "=GradesExamscharts!R8C1:R8C3"

'Increment counters
intRowStart = intRowStart + 6
intRowEnd = intRowEnd + 6
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
Data looks like this
EAST 0000 +AMER LIT-H 49 28% 100 56% 26 15% 3 2% 0 0% 178 100%
EAST 0000 +AMER LIT-H 104 59% 68 38% 4 2% 1 1% 0% 177 100%
SOUTH 0000 +AMER LIT-H 46 22% 125 59% 38 18% 2 1% 0 0% 211 100%
SOUTH 0000 +AMER LIT-H 117 55% 82 39% 12 6% 0% 0% 211 100%
WEST 0000 +AMER LIT-H 34 19% 103 59% 38 22% 0 0% 0 0% 175 100%
WEST 0000 +AMER LIT-H 82 47% 82 47% 10 6% 1 1% 0% 175 100%
District 0000 +AMER LIT-H Exams 129 23% 328 58% 102 18% 5 1% 0 0% 564 100%
District 0000 +AMER LIT-H Crs Grd 303 54% 232 41% 26 5% 2 0% 0 0% 563 100%
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top