Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
PLEASE help Bernie are you still available?
Jackie,
It could be that column F is empty below that row, or some other limit is reached. The simplest solution would be to split your workbook into two by copying it, then deleting rows from one and not the other, and vice versa. HTH, Bernie MS Excel MVP "Jackie" wrote in message ... Bernie: It was a beautiful thing, looks great! BUT.... for some reason it is stopping on line 385. I still have @ 250 more lines for it to read. Can you think of a reason it would do this? Would the worksheet help at this point? "Jackie" wrote: If you have any ideas, please help! I have the following code which works exactly as I need it to EXCEPT that it does not change the data or chart titles. I am VERY new to VB and can't figure out how to write the code to have the data loop. The code produces 12 pages with four charts to a page but they are all titled the same with the same data. Any suggestions? Sub correctchartmaker() ' ' correctchartmaker Macro ' Macro recorded 12/15/2006 by Jackie ' '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 forloop 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 (Sheets(2).UsedRange.Rows.Count / 2) + 1 'Check if new sheet needs to be created If intSheetChartCount = 1 Then 'Create a blank sheet Worksheets.Add.Name = "Page" & intSheetCount 'Name Sheet strSheetName = "Page" & 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.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!R2C6,GradesExamscharts!R2C8,G radesExamscharts!R2C10,GradesExamscharts!R2C12,Gra desExamscharts!R2C14)" ActiveChart.SeriesCollection(2).Name = "=GradesExamscharts!R9C4" ActiveChart.Location Whe=xlLocationAsObject, Name:=strSheetName With ActiveChart .HasTitle = True .ChartTitle.Characters.Text = 'GradesExamscharts'!R & intRowStart C2:R & intRowStart C3:R .ChartTitle.Select .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 '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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Bernie Deitrick | Excel Discussion (Misc queries) | |||
Thanks a ton, Bernie! | Excel Programming | |||
Thanks Bernie | Excel Programming | |||
Bernie My New Macro | Excel Worksheet Functions | |||
Bernie Deitrick | Excel Worksheet Functions |