Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.charting
|
|||
|
|||
How to put these two together?
I have a spreadsheet that I want to produce charts from. I know how to
find the addresses of ranges that I want to produce charts for. And I have the codes to create a chart for one data range in the spreadsheet. Now, how do I tie them together so that the charts will be created by one macro? Addresses of ranges to chart =============================== Set rStart = Range("A1") grp = rStart.Value i = 2 Do While Cells(i - 1, 1) < "" If Cells(i, 1) < grp Then Set rng = Range("B1", Cells(i - 1, 4)) MsgBox " Addresses for graphing a " & rng.Address(0, 0) Set rStart = Cells(i, 1) grp = rStart.Value End If i = i + 1 Sheets("Chart").Select Loop Macro for the chart ============== Charts.Add ActiveChart.ChartType = xlLineMarkers ActiveChart.SetSourceData Source:=Sheets("Chart").Range(B2:D4), PlotBy:=xlColumns ActiveChart.SeriesCollection(1).ChartType = xlColumnClustered ActiveChart.SeriesCollection(1).Name = "=""Maximum""" ActiveChart.SeriesCollection(2).Name = "=""95th""" ActiveChart.SeriesCollection(3).Name = "=""5th""" ActiveChart.SeriesCollection(2).Points(1).ApplyDat aLabels ShowValue:=True ActiveChart.SeriesCollection(3).Points(1).ApplyDat aLabels ShowValue:=True With ActiveChart .HasTitle = True .ChartTitle.Characters.Text = "Employee Survey" .Axes(xlCategory, xlPrimary).HasTitle = False .Axes(xlValue, xlPrimary).HasTitle = False End With Thanks in advance. Faye Larson |
#2
Posted to microsoft.public.excel.charting
|
|||
|
|||
How to put these two together?
Hi Faye -
Your loop simply finds the range in B:D until it encounters a blank in column A, and you don't need a loop for that. It looks like you have no specific category values or labels; did you want to use column A for this? Sub ChartMyData() dim iLast as long iLast = Worksheets("Chart").Range("A1").End(xlDown).Row Charts.Add With ActiveChart .ChartType = xlLineMarkers .SetSourceData Source:=Worksheets("Chart").Range("B1:D" & iLast), PlotBy:=xlColumns With .SeriesCollection(1) .ChartType = xlColumnClustered .Name = "=""Maximum""" End With With .SeriesCollection(2) .Name = "=""95th""" .Points(1).ApplyDataLabels ShowValue:=True End With With .SeriesCollection(3) .Name = "=""5th""" .Points(1).ApplyDataLabels ShowValue:=True End With .HasTitle = True .ChartTitle.Characters.Text = "Employee Survey" '' may not need these two; they're defaults .Axes(xlCategory, xlPrimary).HasTitle = False .Axes(xlValue, xlPrimary).HasTitle = False End With End Sub - Jon ------- Jon Peltier, Microsoft Excel MVP Peltier Technical Services Tutorials and Custom Solutions http://PeltierTech.com/ _______ "Faye" wrote in message oups.com... I have a spreadsheet that I want to produce charts from. I know how to find the addresses of ranges that I want to produce charts for. And I have the codes to create a chart for one data range in the spreadsheet. Now, how do I tie them together so that the charts will be created by one macro? Addresses of ranges to chart =============================== Set rStart = Range("A1") grp = rStart.Value i = 2 Do While Cells(i - 1, 1) < "" If Cells(i, 1) < grp Then Set rng = Range("B1", Cells(i - 1, 4)) MsgBox " Addresses for graphing a " & rng.Address(0, 0) Set rStart = Cells(i, 1) grp = rStart.Value End If i = i + 1 Sheets("Chart").Select Loop Macro for the chart ============== Charts.Add ActiveChart.ChartType = xlLineMarkers ActiveChart.SetSourceData Source:=Sheets("Chart").Range(B2:D4), PlotBy:=xlColumns ActiveChart.SeriesCollection(1).ChartType = xlColumnClustered ActiveChart.SeriesCollection(1).Name = "=""Maximum""" ActiveChart.SeriesCollection(2).Name = "=""95th""" ActiveChart.SeriesCollection(3).Name = "=""5th""" ActiveChart.SeriesCollection(2).Points(1).ApplyDat aLabels ShowValue:=True ActiveChart.SeriesCollection(3).Points(1).ApplyDat aLabels ShowValue:=True With ActiveChart .HasTitle = True .ChartTitle.Characters.Text = "Employee Survey" .Axes(xlCategory, xlPrimary).HasTitle = False .Axes(xlValue, xlPrimary).HasTitle = False End With Thanks in advance. Faye Larson |
#3
Posted to microsoft.public.excel.charting
|
|||
|
|||
How to put these two together?
I need to loop through the data because I need to plot a chart for
every group of value in Column A. For example the following sample data shows that it should produce 4 charts for each person in Column A, for the value from B to D. A B C D 1 FAYE 8965 56342 22969 2 FAYE 78515 332566 122443 3 FAYE 63903 324033 75769 4 FAYE 61027 324078 74577 5 FAYE 60624 323889 100977 6 JEFF 60103 329421 111792 7 JEFF 56807 321219 122607 8 Jean 57618 319549 133422 9 Jean 58433 321294 144237 10 Jean 59119 322247 155052 11 Jean 60971 318629 165867 12 mark 62601 314424 176682 13 mark 65245 318827 187497 14 mark 64662 320495 198312 .... .... I hope I have clarified my question. Your help is greatly appreciated. Faye Larson |
#4
Posted to microsoft.public.excel.charting
|
|||
|
|||
How to put these two together?
Faye -
Oh, I completely missed the If Cells(i, 1) < grp Then in your first macro. So each person has a chart; each chart shows three series, for the columns B, C, and D; such that Faye's chart has 5 points, Jeff's has 2, etc. You need to loop, as you've done, and as long as the cell in column A is the same, keep counting. When it changes, make a chart. If it's not zero, reset the top of the next block of data and continue. Like this, though you may need to fine tune the series names and data labels. Sub MakePlots() Dim rTop As Range Dim rChart As Range Dim ChtOb As ChartObject Dim iRow As Long Dim dTop As Double, dLeft As Double Dim dHeight As Double, dWidth As Double dHeight = 150 dWidth = 250 Set rTop = ActiveSheet.Range("A1") Set rChart = ActiveSheet.Range("B1:D1") iRow = 0 Do iRow = iRow + 1 If ActiveSheet.Range("A" & iRow).Value = rTop.Value Then ' continue adding to chart range Set rChart = ActiveSheet.Range("B" & rTop.Row & ":D" & iRow) Else ' chart what we've collected dTop = ActiveSheet.Range("A" & rTop.Row).Top dLeft = ActiveSheet.Range("E1").Left + (rTop.Row - 1) * 5 Set ChtOb = ActiveSheet.ChartObjects.Add(dLeft, dTop, dWidth, dHeight) With ChtOb.Chart .ChartType = xlLineMarkers .SetSourceData Source:=rChart, PlotBy:=xlColumns With .SeriesCollection(1) .ChartType = xlColumnClustered .Name = "=""Maximum""" End With With .SeriesCollection(2) .Name = "=""95th""" .Points(1).ApplyDataLabels ShowValue:=True End With With .SeriesCollection(3) .Name = "=""5th""" .Points(1).ApplyDataLabels ShowValue:=True End With .HasTitle = True .ChartTitle.Characters.Text = "Employee Survey - " & rTop.Value End With ' quit or reset rTop If Len(ActiveSheet.Range("A" & iRow).Value) = 0 Then Exit Do Set rTop = ActiveSheet.Range("A" & iRow) Set rChart = ActiveSheet.Range("B" & iRow & ":D" & iRow) End If Loop End Sub - Jon ------- Jon Peltier, Microsoft Excel MVP Peltier Technical Services Tutorials and Custom Solutions http://PeltierTech.com/ _______ "Faye" wrote in message oups.com... I need to loop through the data because I need to plot a chart for every group of value in Column A. For example the following sample data shows that it should produce 4 charts for each person in Column A, for the value from B to D. A B C D 1 FAYE 8965 56342 22969 2 FAYE 78515 332566 122443 3 FAYE 63903 324033 75769 4 FAYE 61027 324078 74577 5 FAYE 60624 323889 100977 6 JEFF 60103 329421 111792 7 JEFF 56807 321219 122607 8 Jean 57618 319549 133422 9 Jean 58433 321294 144237 10 Jean 59119 322247 155052 11 Jean 60971 318629 165867 12 mark 62601 314424 176682 13 mark 65245 318827 187497 14 mark 64662 320495 198312 ... ... I hope I have clarified my question. Your help is greatly appreciated. Faye Larson |
#5
Posted to microsoft.public.excel.charting
|
|||
|
|||
How to put these two together?
Thanks, Jon. Your code not only provided the solution but also a very
good start for me to learn to code the proper way. Thanks again. Faye Larson |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|