Home |
Search |
Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Help with For - Next Loop
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 Whe=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,G radesExamscharts!R1C10,GradesExamscharts!R1C12,Gra desExamscharts!R1C14)" ActiveChart.SeriesCollection(1).Name = "=GradesExamscharts!R8C4" ActiveChart.SeriesCollection(2).XValues = _ "=(GradesExamscharts!R1C6,GradesExamscharts!R1C8,G radesExamscharts!R1C10,GradesExamscharts!R1C12,Gra desExamscharts!R1C14)" ActiveChart.SeriesCollection(2).Name = "=GradesExamscharts!R9C4" ActiveChart.Location Whe=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% |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
For Each ... Next loop - need to reference the loop variable | Excel Programming | |||
Advancing outer Loop Based on criteria of inner loop | Excel Programming | |||
Loop Function unable to loop | Excel Programming | |||
Problem adding charts using Do-Loop Until loop | Excel Programming | |||
HELP!!!! Can't stop a loop (NOT an infinite loop) | Excel Programming |