If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Macro to create charts?
I recorded a macro to create charts using the Wizard, but ran into a
problem. My X-axis is in Col. A. The label is in A1, and the data starts in A5 (time). I modified the macro to use the last data row, and I also modified it to use other ranges for data, keeping Col A as my X-axis. This version, though, gave me fits on one particular sheet. Cols B - J are data to be plotted by the lines in the chart. On one worksheet, however, the sensor for the Col B data didn't record, and the recorder program filled the column with "NONE". The Wizard and the macro *insisted* on grouping A and B together for my X-axis on this sheet, though it worked okay for the other sheets. Looking at the macro, I realized there isn't anything (that I can see) that specifies which column is the X-axis Is there a better way to write this? Ed Sub Macro9() ' Dim LastRow As Long LastRow = ActiveWorkbook.Sheets("Sheet1").Range("A65536").En d(xlUp).Row Charts.Add ActiveChart.ChartType = xlLine ActiveChart.SetSourceData Source:=Sheets("Sheet1").Range( _ "A1,B1:J1,A5:A" & LastRow & ",B5:J" & LastRow), PlotBy:=xlColumns ActiveChart.Location Whe=xlLocationAsNewSheet With ActiveChart.Axes(xlCategory) .HasMajorGridlines = False .HasMinorGridlines = False End With With ActiveChart.Axes(xlValue) .HasMajorGridlines = True .HasMinorGridlines = False End With End Sub |
#2
|
|||
|
|||
Macro to create charts?
Ed -
Omit the SetSourceData command. Instead, add each series and set its source data separately: Sub DoChart() Dim LastRow As Long Dim iSrs As Long LastRow = 10 With Charts.Add ' Remove Extraneous Series Do While .SeriesCollection.Count 0 .SeriesCollection(1).Delete Loop .ChartType = xlLine ' Add Series For iSrs = 1 To 9 With .SeriesCollection.NewSeries .Values = Sheets("Sheet1").Range("A5:A" & LastRow).Offset(, iSrs) .XValues = Sheets("Sheet1").Range("A5:A" & LastRow) .Name = Sheets("Sheet1").Range("A1").Offset(, iSrs) End With Next End With End Sub More on VBA charting: http://peltiertech.com/Excel/ChartsH...kChartVBA.html - Jon ------- Jon Peltier, Microsoft Excel MVP Tutorials and Custom Solutions http://PeltierTech.com _______ "Ed" wrote in message ... I recorded a macro to create charts using the Wizard, but ran into a problem. My X-axis is in Col. A. The label is in A1, and the data starts in A5 (time). I modified the macro to use the last data row, and I also modified it to use other ranges for data, keeping Col A as my X-axis. This version, though, gave me fits on one particular sheet. Cols B - J are data to be plotted by the lines in the chart. On one worksheet, however, the sensor for the Col B data didn't record, and the recorder program filled the column with "NONE". The Wizard and the macro *insisted* on grouping A and B together for my X-axis on this sheet, though it worked okay for the other sheets. Looking at the macro, I realized there isn't anything (that I can see) that specifies which column is the X-axis Is there a better way to write this? Ed Sub Macro9() ' Dim LastRow As Long LastRow = ActiveWorkbook.Sheets("Sheet1").Range("A65536").En d(xlUp).Row Charts.Add ActiveChart.ChartType = xlLine ActiveChart.SetSourceData Source:=Sheets("Sheet1").Range( _ "A1,B1:J1,A5:A" & LastRow & ",B5:J" & LastRow), PlotBy:=xlColumns ActiveChart.Location Whe=xlLocationAsNewSheet With ActiveChart.Axes(xlCategory) .HasMajorGridlines = False .HasMinorGridlines = False End With With ActiveChart.Axes(xlValue) .HasMajorGridlines = True .HasMinorGridlines = False End With End Sub |
#3
|
|||
|
|||
Macro to create charts?
Thank you, Jon. I appreciate the help.
Ed "Jon Peltier" wrote in message ... Ed - Omit the SetSourceData command. Instead, add each series and set its source data separately: Sub DoChart() Dim LastRow As Long Dim iSrs As Long LastRow = 10 With Charts.Add ' Remove Extraneous Series Do While .SeriesCollection.Count 0 .SeriesCollection(1).Delete Loop .ChartType = xlLine ' Add Series For iSrs = 1 To 9 With .SeriesCollection.NewSeries .Values = Sheets("Sheet1").Range("A5:A" & LastRow).Offset(, iSrs) .XValues = Sheets("Sheet1").Range("A5:A" & LastRow) .Name = Sheets("Sheet1").Range("A1").Offset(, iSrs) End With Next End With End Sub More on VBA charting: http://peltiertech.com/Excel/ChartsH...kChartVBA.html - Jon ------- Jon Peltier, Microsoft Excel MVP Tutorials and Custom Solutions http://PeltierTech.com _______ "Ed" wrote in message ... I recorded a macro to create charts using the Wizard, but ran into a problem. My X-axis is in Col. A. The label is in A1, and the data starts in A5 (time). I modified the macro to use the last data row, and I also modified it to use other ranges for data, keeping Col A as my X-axis. This version, though, gave me fits on one particular sheet. Cols B - J are data to be plotted by the lines in the chart. On one worksheet, however, the sensor for the Col B data didn't record, and the recorder program filled the column with "NONE". The Wizard and the macro *insisted* on grouping A and B together for my X-axis on this sheet, though it worked okay for the other sheets. Looking at the macro, I realized there isn't anything (that I can see) that specifies which column is the X-axis Is there a better way to write this? Ed Sub Macro9() ' Dim LastRow As Long LastRow = ActiveWorkbook.Sheets("Sheet1").Range("A65536").En d(xlUp).Row Charts.Add ActiveChart.ChartType = xlLine ActiveChart.SetSourceData Source:=Sheets("Sheet1").Range( _ "A1,B1:J1,A5:A" & LastRow & ",B5:J" & LastRow), PlotBy:=xlColumns ActiveChart.Location Whe=xlLocationAsNewSheet With ActiveChart.Axes(xlCategory) .HasMajorGridlines = False .HasMinorGridlines = False End With With ActiveChart.Axes(xlValue) .HasMajorGridlines = True .HasMinorGridlines = False End With End Sub |
Thread Tools | |
Display Modes | |
|
|