| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Simple VBA Question
Currently using the following code to grab an adjacent column:
Set rngYData = rngXData.Offset(0,1) later on in the program: ..XValues=rngXdata ..Values=rngYdata The above allows me to plot the following data: County Year Estimate1 a 1981 500 a 1982 650 I would like to be able to add another series (or 2) and plot it. County Year Estimate1 Estimate2 Estimate3 a 1981 500 590 600 a 1982 650 750 800 Can I adapt the above code to allow me to do this? Mike |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Simple VBA Question
Try
Set rngYData = rngXData.Offset(0,1).Resize(,3) HTH, Bernie MS Excel MVP wrote in message ups.com... Currently using the following code to grab an adjacent column: Set rngYData = rngXData.Offset(0,1) later on in the program: .XValues=rngXdata .Values=rngYdata The above allows me to plot the following data: County Year Estimate1 a 1981 500 a 1982 650 I would like to be able to add another series (or 2) and plot it. County Year Estimate1 Estimate2 Estimate3 a 1981 500 590 600 a 1982 650 750 800 Can I adapt the above code to allow me to do this? Mike |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Simple VBA Question
Bernie - Hey, thanks for trying to help. I guess its not that simple.
I got "An Unable to Set the Values of the Series Class" error message. If you have a chance, would you mind looking at the code below and see if you can't figure out how I might tweak it to plot 2 series? The code works great for 1 series. I would greatly appreciate your help. Mike Dim lngRow As Long Dim lngStartRow As Long Dim chtDeer As Chart Dim shtData As Worksheet Dim rngXData As Range Dim rngYData As Range Dim strCounty As String ' Set shtData = Worksheets("Sheet1") lngRow = 2 lngStartRow = 2 Do While shtData.Cells(lngRow, 1) < "" If shtData.Cells(lngRow, 1) < shtData.Cells(lngRow + 1, 1) Then Set rngXData = shtData.Range( _ "B" & lngStartRow & ":B" & lngRow) 'Set rngYData = rngXData.Offset(0, 1) Set rngYData = rngXData.Offset(0, 1).Resize(, 3) strCounty = shtData.Cells(lngRow, 1).Value ' make a chart Set chtDeer = Charts.Add With chtDeer Do While .SeriesCollection.Count 0 .SeriesCollection(1).Delete Loop .ChartType = xlXYScatterLines .PlotBy = xlColumns With .SeriesCollection.NewSeries .XValues = rngXData .Values = rngYData End With .Location Whe=xlLocationAsNewSheet .HasTitle = True .ChartTitle.Characters.Text = strCounty .Axes(xlCategory, xlPrimary).HasTitle = False .Axes(xlValue, xlPrimary).HasTitle = False .HasLegend = False .Name = strCounty End With lngStartRow = lngRow + 1 End If lngRow = lngRow + 1 Loop Set rngXData = Nothing Set rngYData = Nothing Set shtData = Nothing Set chtDeer = Nothing End Sub Bernie Deitrick wrote: Try Set rngYData = rngXData.Offset(0,1).Resize(,3) HTH, Bernie MS Excel MVP wrote in message ups.com... Currently using the following code to grab an adjacent column: Set rngYData = rngXData.Offset(0,1) later on in the program: .XValues=rngXdata .Values=rngYdata The above allows me to plot the following data: County Year Estimate1 a 1981 500 a 1982 650 I would like to be able to add another series (or 2) and plot it. County Year Estimate1 Estimate2 Estimate3 a 1981 500 590 600 a 1982 650 750 800 Can I adapt the above code to allow me to do this? Mike |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Simple VBA Question
Mike,
No need to do it series by series. Try this instead: Sub TryNow() Dim chtDeer As Chart Dim shtData As Worksheet Dim rngData As Range Dim strCounty As String Set shtData = Worksheets("Sheet1") Set rngData = shtData.Range("B2", _ shtData.Range("B65536").End(xlUp)).Resize(, 4) strCounty = shtData.Range("A65536").End(xlUp).Value ' make a chart Set chtDeer = Charts.Add With chtDeer .ChartType = xlXYScatterLines .SetSourceData Source:=rngData, PlotBy:=xlColumns .Location Whe=xlLocationAsNewSheet .HasTitle = True .ChartTitle.Characters.Text = strCounty .Axes(xlCategory, xlPrimary).HasTitle = False .Axes(xlValue, xlPrimary).HasTitle = False .HasLegend = False .Name = strCounty End With End Sub -- HTH, Bernie MS Excel MVP wrote in message ups.com... Bernie - Hey, thanks for trying to help. I guess its not that simple. I got "An Unable to Set the Values of the Series Class" error message. If you have a chance, would you mind looking at the code below and see if you can't figure out how I might tweak it to plot 2 series? The code works great for 1 series. I would greatly appreciate your help. Mike Dim lngRow As Long Dim lngStartRow As Long Dim chtDeer As Chart Dim shtData As Worksheet Dim rngXData As Range Dim rngYData As Range Dim strCounty As String ' Set shtData = Worksheets("Sheet1") lngRow = 2 lngStartRow = 2 Do While shtData.Cells(lngRow, 1) < "" If shtData.Cells(lngRow, 1) < shtData.Cells(lngRow + 1, 1) Then Set rngXData = shtData.Range( _ "B" & lngStartRow & ":B" & lngRow) 'Set rngYData = rngXData.Offset(0, 1) Set rngYData = rngXData.Offset(0, 1).Resize(, 3) strCounty = shtData.Cells(lngRow, 1).Value ' make a chart Set chtDeer = Charts.Add With chtDeer Do While .SeriesCollection.Count 0 .SeriesCollection(1).Delete Loop .ChartType = xlXYScatterLines .PlotBy = xlColumns With .SeriesCollection.NewSeries .XValues = rngXData .Values = rngYData End With .Location Whe=xlLocationAsNewSheet .HasTitle = True .ChartTitle.Characters.Text = strCounty .Axes(xlCategory, xlPrimary).HasTitle = False .Axes(xlValue, xlPrimary).HasTitle = False .HasLegend = False .Name = strCounty End With lngStartRow = lngRow + 1 End If lngRow = lngRow + 1 Loop Set rngXData = Nothing Set rngYData = Nothing Set shtData = Nothing Set chtDeer = Nothing End Sub Bernie Deitrick wrote: Try Set rngYData = rngXData.Offset(0,1).Resize(,3) HTH, Bernie MS Excel MVP wrote in message ups.com... Currently using the following code to grab an adjacent column: Set rngYData = rngXData.Offset(0,1) later on in the program: .XValues=rngXdata .Values=rngYdata The above allows me to plot the following data: County Year Estimate1 a 1981 500 a 1982 650 I would like to be able to add another series (or 2) and plot it. County Year Estimate1 Estimate2 Estimate3 a 1981 500 590 600 a 1982 650 750 800 Can I adapt the above code to allow me to do this? Mike |
|
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Simple VBA Question
Bernie - No luck. I've included a small chunk of data so you can see
the problems. The goal is to create one new sheet for each county. The code below creates a sheet for Allen County only. While it is plotting both series, it is plotting data for both counties on the same chart. We are getting closer, nonetheless. County Year Total Test Adams 1981 898.8156935 1797.631387 Adams 1982 813.9700179 1627.940036 Adams 1983 600.085855 1200.17171 Adams 1984 168.0186444 336.0372887 Adams 1985 -419.0474403 -838.0948806 Adams 1986 -1076.684463 -2153.368927 Adams 1987 -2013.038366 -4026.076732 Adams 1988 -3828.642918 -7657.285835 Adams 1989 -6414.945724 -12829.89145 Adams 1990 -9823.533193 -19647.06639 Adams 1991 -14595.55507 -29191.11014 Adams 1992 -20926.46511 -41852.93022 Adams 1993 -29545.58061 -59091.16121 Adams 1994 -41193.75363 -82387.50726 Adams 1995 -57186.93091 -114373.8618 Adams 1996 -78821.69682 -157643.3936 Adams 1997 -107203.0308 -214406.0617 Adams 1998 -144686.6027 -289373.2055 Adams 1999 -193074.2313 -386148.4625 Adams 2000 -257022.6462 -514045.2923 Adams 2001 -341873.5824 -683747.1649 Adams 2002 -455768.9238 -911537.8475 Adams 2003 -607328.0552 -1214656.11 Adams 2004 -808423.6442 -1616847.288 Adams 2005 -1075474.604 -2150949.209 Allen 1981 468.7313814 937.4627629 Allen 1982 490.7175253 981.4350507 Allen 1983 522.2108734 1044.421747 Allen 1984 577.0034302 1154.00686 Allen 1985 674.5462361 1349.092472 Allen 1986 816.1476042 1632.295208 Allen 1987 967.4184287 1934.836857 Allen 1988 1146.779498 2293.558996 Allen 1989 1308.530843 2617.061687 Allen 1990 1382.579392 2765.158784 Allen 1991 1437.806471 2875.612942 Allen 1992 1428.017453 2856.034906 Allen 1993 1401.219851 2802.439703 Allen 1994 1068.883896 2137.767793 Allen 1995 542.5802303 1085.160461 Allen 1996 -24.0678335 -48.13566699 Allen 1997 -355.5936444 -711.1872888 Allen 1998 -730.2881363 -1460.576273 Allen 1999 -1177.553273 -2355.106546 Allen 2000 -1793.125099 -3586.250197 Allen 2001 -2712.182518 -5424.365036 Allen 2002 -4045.637628 -8091.275255 Allen 2003 -5952.153357 -11904.30671 Allen 2004 -8215.624829 -16431.24966 Allen 2005 -11338.86714 -22677.73428 Bernie Deitrick wrote: Mike, No need to do it series by series. Try this instead: Sub TryNow() Dim chtDeer As Chart Dim shtData As Worksheet Dim rngData As Range Dim strCounty As String Set shtData = Worksheets("Sheet1") Set rngData = shtData.Range("B2", _ shtData.Range("B65536").End(xlUp)).Resize(, 4) strCounty = shtData.Range("A65536").End(xlUp).Value ' make a chart Set chtDeer = Charts.Add With chtDeer .ChartType = xlXYScatterLines .SetSourceData Source:=rngData, PlotBy:=xlColumns .Location Whe=xlLocationAsNewSheet .HasTitle = True .ChartTitle.Characters.Text = strCounty .Axes(xlCategory, xlPrimary).HasTitle = False .Axes(xlValue, xlPrimary).HasTitle = False .HasLegend = False .Name = strCounty End With End Sub -- HTH, Bernie MS Excel MVP wrote in message ups.com... Bernie - Hey, thanks for trying to help. I guess its not that simple. I got "An Unable to Set the Values of the Series Class" error message. If you have a chance, would you mind looking at the code below and see if you can't figure out how I might tweak it to plot 2 series? The code works great for 1 series. I would greatly appreciate your help. Mike Dim lngRow As Long Dim lngStartRow As Long Dim chtDeer As Chart Dim shtData As Worksheet Dim rngXData As Range Dim rngYData As Range Dim strCounty As String ' Set shtData = Worksheets("Sheet1") lngRow = 2 lngStartRow = 2 Do While shtData.Cells(lngRow, 1) < "" If shtData.Cells(lngRow, 1) < shtData.Cells(lngRow + 1, 1) Then Set rngXData = shtData.Range( _ "B" & lngStartRow & ":B" & lngRow) 'Set rngYData = rngXData.Offset(0, 1) Set rngYData = rngXData.Offset(0, 1).Resize(, 3) strCounty = shtData.Cells(lngRow, 1).Value ' make a chart Set chtDeer = Charts.Add With chtDeer Do While .SeriesCollection.Count 0 .SeriesCollection(1).Delete Loop .ChartType = xlXYScatterLines .PlotBy = xlColumns With .SeriesCollection.NewSeries .XValues = rngXData .Values = rngYData End With .Location Whe=xlLocationAsNewSheet .HasTitle = True .ChartTitle.Characters.Text = strCounty .Axes(xlCategory, xlPrimary).HasTitle = False .Axes(xlValue, xlPrimary).HasTitle = False .HasLegend = False .Name = strCounty End With lngStartRow = lngRow + 1 End If lngRow = lngRow + 1 Loop Set rngXData = Nothing Set rngYData = Nothing Set shtData = Nothing Set chtDeer = Nothing End Sub Bernie Deitrick wrote: Try Set rngYData = rngXData.Offset(0,1).Resize(,3) HTH, Bernie MS Excel MVP wrote in message ups.com... Currently using the following code to grab an adjacent column: Set rngYData = rngXData.Offset(0,1) later on in the program: .XValues=rngXdata .Values=rngYdata The above allows me to plot the following data: County Year Estimate1 a 1981 500 a 1982 650 I would like to be able to add another series (or 2) and plot it. County Year Estimate1 Estimate2 Estimate3 a 1981 500 590 600 a 1982 650 750 800 Can I adapt the above code to allow me to do this? Mike |
|
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Simple VBA Question
Mike,
Try the macro below. Assumes that your data starts in cell A1 of sheet Sheet1, and is contiguous. HTH, Bernie MS Excel MVP Sub GraphByUniqueCategory() Dim myList() As Variant Dim i As Integer Dim j As Integer Dim myCount As Integer Dim chtDeer As Chart Dim shtData As Worksheet Dim rngData As Range Dim myDataSet As Range Dim strCounty As String myCount = 1 Set shtData = Worksheets("Sheet1") With shtData.Range("A2").CurrentRegion.Columns(1) ..AdvancedFilter Action:=xlFilterInPlace, Unique:=True ReDim myList(1 To .SpecialCells(xlCellTypeVisible).Count) With .SpecialCells(xlCellTypeVisible) For j = 1 To .Areas.Count For i = 1 To .Areas(j).Cells.Count myList(myCount) = .Areas(j).Cells(i).Value myCount = myCount + 1 Next i Next j End With ActiveSheet.ShowAllData End With Set myDataSet = shtData.Range("B2").CurrentRegion For i = LBound(myList) + 1 To UBound(myList) MsgBox "Now doing " & myList(i) shtData.Range("A2").AutoFilter Field:=1, Criteria1:=myList(i) Set rngData = Intersect(myDataSet, shtData.Range("B:E").SpecialCells(xlCellTypeVisibl e)) strCounty = shtData.Range("A65536").End(xlUp).Value ' make a chart Set chtDeer = Charts.Add With chtDeer .ChartType = xlXYScatterLines .SetSourceData Source:=rngData, PlotBy:=xlColumns .Location Whe=xlLocationAsNewSheet .HasTitle = True .ChartTitle.Characters.Text = strCounty .Axes(xlCategory, xlPrimary).HasTitle = False .Axes(xlValue, xlPrimary).HasTitle = False .HasLegend = False .Name = strCounty End With Next i shtData.ShowAllData End Sub wrote in message oups.com... Bernie - No luck. I've included a small chunk of data so you can see the problems. The goal is to create one new sheet for each county. The code below creates a sheet for Allen County only. While it is plotting both series, it is plotting data for both counties on the same chart. We are getting closer, nonetheless. County Year Total Test Adams 1981 898.8156935 1797.631387 Adams 1982 813.9700179 1627.940036 Adams 1983 600.085855 1200.17171 Adams 1984 168.0186444 336.0372887 Adams 1985 -419.0474403 -838.0948806 Adams 1986 -1076.684463 -2153.368927 Adams 1987 -2013.038366 -4026.076732 Adams 1988 -3828.642918 -7657.285835 Adams 1989 -6414.945724 -12829.89145 Adams 1990 -9823.533193 -19647.06639 Adams 1991 -14595.55507 -29191.11014 Adams 1992 -20926.46511 -41852.93022 Adams 1993 -29545.58061 -59091.16121 Adams 1994 -41193.75363 -82387.50726 Adams 1995 -57186.93091 -114373.8618 Adams 1996 -78821.69682 -157643.3936 Adams 1997 -107203.0308 -214406.0617 Adams 1998 -144686.6027 -289373.2055 Adams 1999 -193074.2313 -386148.4625 Adams 2000 -257022.6462 -514045.2923 Adams 2001 -341873.5824 -683747.1649 Adams 2002 -455768.9238 -911537.8475 Adams 2003 -607328.0552 -1214656.11 Adams 2004 -808423.6442 -1616847.288 Adams 2005 -1075474.604 -2150949.209 Allen 1981 468.7313814 937.4627629 Allen 1982 490.7175253 981.4350507 Allen 1983 522.2108734 1044.421747 Allen 1984 577.0034302 1154.00686 Allen 1985 674.5462361 1349.092472 Allen 1986 816.1476042 1632.295208 Allen 1987 967.4184287 1934.836857 Allen 1988 1146.779498 2293.558996 Allen 1989 1308.530843 2617.061687 Allen 1990 1382.579392 2765.158784 Allen 1991 1437.806471 2875.612942 Allen 1992 1428.017453 2856.034906 Allen 1993 1401.219851 2802.439703 Allen 1994 1068.883896 2137.767793 Allen 1995 542.5802303 1085.160461 Allen 1996 -24.0678335 -48.13566699 Allen 1997 -355.5936444 -711.1872888 Allen 1998 -730.2881363 -1460.576273 Allen 1999 -1177.553273 -2355.106546 Allen 2000 -1793.125099 -3586.250197 Allen 2001 -2712.182518 -5424.365036 Allen 2002 -4045.637628 -8091.275255 Allen 2003 -5952.153357 -11904.30671 Allen 2004 -8215.624829 -16431.24966 Allen 2005 -11338.86714 -22677.73428 Bernie Deitrick wrote: Mike, No need to do it series by series. Try this instead: Sub TryNow() Dim chtDeer As Chart Dim shtData As Worksheet Dim rngData As Range Dim strCounty As String Set shtData = Worksheets("Sheet1") Set rngData = shtData.Range("B2", _ shtData.Range("B65536").End(xlUp)).Resize(, 4) strCounty = shtData.Range("A65536").End(xlUp).Value ' make a chart Set chtDeer = Charts.Add With chtDeer .ChartType = xlXYScatterLines .SetSourceData Source:=rngData, PlotBy:=xlColumns .Location Whe=xlLocationAsNewSheet .HasTitle = True .ChartTitle.Characters.Text = strCounty .Axes(xlCategory, xlPrimary).HasTitle = False .Axes(xlValue, xlPrimary).HasTitle = False .HasLegend = False .Name = strCounty End With End Sub -- HTH, Bernie MS Excel MVP wrote in message ups.com... Bernie - Hey, thanks for trying to help. I guess its not that simple. I got "An Unable to Set the Values of the Series Class" error message. If you have a chance, would you mind looking at the code below and see if you can't figure out how I might tweak it to plot 2 series? The code works great for 1 series. I would greatly appreciate your help. Mike Dim lngRow As Long Dim lngStartRow As Long Dim chtDeer As Chart Dim shtData As Worksheet Dim rngXData As Range Dim rngYData As Range Dim strCounty As String ' Set shtData = Worksheets("Sheet1") lngRow = 2 lngStartRow = 2 Do While shtData.Cells(lngRow, 1) < "" If shtData.Cells(lngRow, 1) < shtData.Cells(lngRow + 1, 1) Then Set rngXData = shtData.Range( _ "B" & lngStartRow & ":B" & lngRow) 'Set rngYData = rngXData.Offset(0, 1) Set rngYData = rngXData.Offset(0, 1).Resize(, 3) strCounty = shtData.Cells(lngRow, 1).Value ' make a chart Set chtDeer = Charts.Add With chtDeer Do While .SeriesCollection.Count 0 .SeriesCollection(1).Delete Loop .ChartType = xlXYScatterLines .PlotBy = xlColumns With .SeriesCollection.NewSeries .XValues = rngXData .Values = rngYData End With .Location Whe=xlLocationAsNewSheet .HasTitle = True .ChartTitle.Characters.Text = strCounty .Axes(xlCategory, xlPrimary).HasTitle = False .Axes(xlValue, xlPrimary).HasTitle = False .HasLegend = False .Name = strCounty End With lngStartRow = lngRow + 1 End If lngRow = lngRow + 1 Loop Set rngXData = Nothing Set rngYData = Nothing Set shtData = Nothing Set chtDeer = Nothing End Sub Bernie Deitrick wrote: Try Set rngYData = rngXData.Offset(0,1).Resize(,3) HTH, Bernie MS Excel MVP wrote in message ups.com... Currently using the following code to grab an adjacent column: Set rngYData = rngXData.Offset(0,1) later on in the program: .XValues=rngXdata .Values=rngYdata The above allows me to plot the following data: County Year Estimate1 a 1981 500 a 1982 650 I would like to be able to add another series (or 2) and plot it. County Year Estimate1 Estimate2 Estimate3 a 1981 500 590 600 a 1982 650 750 800 Can I adapt the above code to allow me to do this? Mike |
|
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Simple VBA Question
Bernie - you are terrific! I gather that I need to use the macro
recorder to get the graph to look like I want it and then paste the code in the appropriate place. Your help is really appreicated. Thank you! Bernie Deitrick wrote: Mike, Try the macro below. Assumes that your data starts in cell A1 of sheet Sheet1, and is contiguous. HTH, Bernie MS Excel MVP Sub GraphByUniqueCategory() Dim myList() As Variant Dim i As Integer Dim j As Integer Dim myCount As Integer Dim chtDeer As Chart Dim shtData As Worksheet Dim rngData As Range Dim myDataSet As Range Dim strCounty As String myCount = 1 Set shtData = Worksheets("Sheet1") With shtData.Range("A2").CurrentRegion.Columns(1) .AdvancedFilter Action:=xlFilterInPlace, Unique:=True ReDim myList(1 To .SpecialCells(xlCellTypeVisible).Count) With .SpecialCells(xlCellTypeVisible) For j = 1 To .Areas.Count For i = 1 To .Areas(j).Cells.Count myList(myCount) = .Areas(j).Cells(i).Value myCount = myCount + 1 Next i Next j End With ActiveSheet.ShowAllData End With Set myDataSet = shtData.Range("B2").CurrentRegion For i = LBound(myList) + 1 To UBound(myList) MsgBox "Now doing " & myList(i) shtData.Range("A2").AutoFilter Field:=1, Criteria1:=myList(i) Set rngData = Intersect(myDataSet, shtData.Range("B:E").SpecialCells(xlCellTypeVisibl e)) strCounty = shtData.Range("A65536").End(xlUp).Value ' make a chart Set chtDeer = Charts.Add With chtDeer .ChartType = xlXYScatterLines .SetSourceData Source:=rngData, PlotBy:=xlColumns .Location Whe=xlLocationAsNewSheet .HasTitle = True .ChartTitle.Characters.Text = strCounty .Axes(xlCategory, xlPrimary).HasTitle = False .Axes(xlValue, xlPrimary).HasTitle = False .HasLegend = False .Name = strCounty End With Next i shtData.ShowAllData End Sub wrote in message oups.com... Bernie - No luck. I've included a small chunk of data so you can see the problems. The goal is to create one new sheet for each county. The code below creates a sheet for Allen County only. While it is plotting both series, it is plotting data for both counties on the same chart. We are getting closer, nonetheless. County Year Total Test Adams 1981 898.8156935 1797.631387 Adams 1982 813.9700179 1627.940036 Adams 1983 600.085855 1200.17171 Adams 1984 168.0186444 336.0372887 Adams 1985 -419.0474403 -838.0948806 Adams 1986 -1076.684463 -2153.368927 Adams 1987 -2013.038366 -4026.076732 Adams 1988 -3828.642918 -7657.285835 Adams 1989 -6414.945724 -12829.89145 Adams 1990 -9823.533193 -19647.06639 Adams 1991 -14595.55507 -29191.11014 Adams 1992 -20926.46511 -41852.93022 Adams 1993 -29545.58061 -59091.16121 Adams 1994 -41193.75363 -82387.50726 Adams 1995 -57186.93091 -114373.8618 Adams 1996 -78821.69682 -157643.3936 Adams 1997 -107203.0308 -214406.0617 Adams 1998 -144686.6027 -289373.2055 Adams 1999 -193074.2313 -386148.4625 Adams 2000 -257022.6462 -514045.2923 Adams 2001 -341873.5824 -683747.1649 Adams 2002 -455768.9238 -911537.8475 Adams 2003 -607328.0552 -1214656.11 Adams 2004 -808423.6442 -1616847.288 Adams 2005 -1075474.604 -2150949.209 Allen 1981 468.7313814 937.4627629 Allen 1982 490.7175253 981.4350507 Allen 1983 522.2108734 1044.421747 Allen 1984 577.0034302 1154.00686 Allen 1985 674.5462361 1349.092472 Allen 1986 816.1476042 1632.295208 Allen 1987 967.4184287 1934.836857 Allen 1988 1146.779498 2293.558996 Allen 1989 1308.530843 2617.061687 Allen 1990 1382.579392 2765.158784 Allen 1991 1437.806471 2875.612942 Allen 1992 1428.017453 2856.034906 Allen 1993 1401.219851 2802.439703 Allen 1994 1068.883896 2137.767793 Allen 1995 542.5802303 1085.160461 Allen 1996 -24.0678335 -48.13566699 Allen 1997 -355.5936444 -711.1872888 Allen 1998 -730.2881363 -1460.576273 Allen 1999 -1177.553273 -2355.106546 Allen 2000 -1793.125099 -3586.250197 Allen 2001 -2712.182518 -5424.365036 Allen 2002 -4045.637628 -8091.275255 Allen 2003 -5952.153357 -11904.30671 Allen 2004 -8215.624829 -16431.24966 Allen 2005 -11338.86714 -22677.73428 Bernie Deitrick wrote: Mike, No need to do it series by series. Try this instead: Sub TryNow() Dim chtDeer As Chart Dim shtData As Worksheet Dim rngData As Range Dim strCounty As String Set shtData = Worksheets("Sheet1") Set rngData = shtData.Range("B2", _ shtData.Range("B65536").End(xlUp)).Resize(, 4) strCounty = shtData.Range("A65536").End(xlUp).Value ' make a chart Set chtDeer = Charts.Add With chtDeer .ChartType = xlXYScatterLines .SetSourceData Source:=rngData, PlotBy:=xlColumns .Location Whe=xlLocationAsNewSheet .HasTitle = True .ChartTitle.Characters.Text = strCounty .Axes(xlCategory, xlPrimary).HasTitle = False .Axes(xlValue, xlPrimary).HasTitle = False .HasLegend = False .Name = strCounty End With End Sub -- HTH, Bernie MS Excel MVP wrote in message ups.com... Bernie - Hey, thanks for trying to help. I guess its not that simple. I got "An Unable to Set the Values of the Series Class" error message. If you have a chance, would you mind looking at the code below and see if you can't figure out how I might tweak it to plot 2 series? The code works great for 1 series. I would greatly appreciate your help. Mike Dim lngRow As Long Dim lngStartRow As Long Dim chtDeer As Chart Dim shtData As Worksheet Dim rngXData As Range Dim rngYData As Range Dim strCounty As String ' Set shtData = Worksheets("Sheet1") lngRow = 2 lngStartRow = 2 Do While shtData.Cells(lngRow, 1) < "" If shtData.Cells(lngRow, 1) < shtData.Cells(lngRow + 1, 1) Then Set rngXData = shtData.Range( _ "B" & lngStartRow & ":B" & lngRow) 'Set rngYData = rngXData.Offset(0, 1) Set rngYData = rngXData.Offset(0, 1).Resize(, 3) strCounty = shtData.Cells(lngRow, 1).Value ' make a chart Set chtDeer = Charts.Add With chtDeer Do While .SeriesCollection.Count 0 .SeriesCollection(1).Delete Loop .ChartType = xlXYScatterLines .PlotBy = xlColumns With .SeriesCollection.NewSeries .XValues = rngXData .Values = rngYData End With .Location Whe=xlLocationAsNewSheet .HasTitle = True .ChartTitle.Characters.Text = strCounty .Axes(xlCategory, xlPrimary).HasTitle = False .Axes(xlValue, xlPrimary).HasTitle = False .HasLegend = False .Name = strCounty End With lngStartRow = lngRow + 1 End If lngRow = lngRow + 1 Loop Set rngXData = Nothing Set rngYData = Nothing Set shtData = Nothing Set chtDeer = Nothing End Sub Bernie Deitrick wrote: Try Set rngYData = rngXData.Offset(0,1).Resize(,3) HTH, Bernie MS Excel MVP wrote in message ups.com... Currently using the following code to grab an adjacent column: Set rngYData = rngXData.Offset(0,1) later on in the program: .XValues=rngXdata .Values=rngYdata The above allows me to plot the following data: County Year Estimate1 a 1981 500 a 1982 650 I would like to be able to add another series (or 2) and plot it. County Year Estimate1 Estimate2 Estimate3 a 1981 500 590 600 a 1982 650 750 800 Can I adapt the above code to allow me to do this? Mike |
|
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Simple VBA Question
Mike,
I find it easier to format the chart or graph manually, then when I need to make a chart, I copy it using the macro, and change the data source, also with the macro. Or you can use macros to do the formatting. It's all a matter of taste, I guess. I think that there is a way to change to default formatting for the chart, but I'm not a chart expert.... HTH, Bernie MS Excel MVP wrote in message oups.com... Bernie - you are terrific! I gather that I need to use the macro recorder to get the graph to look like I want it and then paste the code in the appropriate place. Your help is really appreicated. Thank you! Bernie Deitrick wrote: Mike, Try the macro below. Assumes that your data starts in cell A1 of sheet Sheet1, and is contiguous. HTH, Bernie MS Excel MVP Sub GraphByUniqueCategory() Dim myList() As Variant Dim i As Integer Dim j As Integer Dim myCount As Integer Dim chtDeer As Chart Dim shtData As Worksheet Dim rngData As Range Dim myDataSet As Range Dim strCounty As String myCount = 1 Set shtData = Worksheets("Sheet1") With shtData.Range("A2").CurrentRegion.Columns(1) .AdvancedFilter Action:=xlFilterInPlace, Unique:=True ReDim myList(1 To .SpecialCells(xlCellTypeVisible).Count) With .SpecialCells(xlCellTypeVisible) For j = 1 To .Areas.Count For i = 1 To .Areas(j).Cells.Count myList(myCount) = .Areas(j).Cells(i).Value myCount = myCount + 1 Next i Next j End With ActiveSheet.ShowAllData End With Set myDataSet = shtData.Range("B2").CurrentRegion For i = LBound(myList) + 1 To UBound(myList) MsgBox "Now doing " & myList(i) shtData.Range("A2").AutoFilter Field:=1, Criteria1:=myList(i) Set rngData = Intersect(myDataSet, shtData.Range("B:E").SpecialCells(xlCellTypeVisibl e)) strCounty = shtData.Range("A65536").End(xlUp).Value ' make a chart Set chtDeer = Charts.Add With chtDeer .ChartType = xlXYScatterLines .SetSourceData Source:=rngData, PlotBy:=xlColumns .Location Whe=xlLocationAsNewSheet .HasTitle = True .ChartTitle.Characters.Text = strCounty .Axes(xlCategory, xlPrimary).HasTitle = False .Axes(xlValue, xlPrimary).HasTitle = False .HasLegend = False .Name = strCounty End With Next i shtData.ShowAllData End Sub wrote in message oups.com... Bernie - No luck. I've included a small chunk of data so you can see the problems. The goal is to create one new sheet for each county. The code below creates a sheet for Allen County only. While it is plotting both series, it is plotting data for both counties on the same chart. We are getting closer, nonetheless. County Year Total Test Adams 1981 898.8156935 1797.631387 Adams 1982 813.9700179 1627.940036 Adams 1983 600.085855 1200.17171 Adams 1984 168.0186444 336.0372887 Adams 1985 -419.0474403 -838.0948806 Adams 1986 -1076.684463 -2153.368927 Adams 1987 -2013.038366 -4026.076732 Adams 1988 -3828.642918 -7657.285835 Adams 1989 -6414.945724 -12829.89145 Adams 1990 -9823.533193 -19647.06639 Adams 1991 -14595.55507 -29191.11014 Adams 1992 -20926.46511 -41852.93022 Adams 1993 -29545.58061 -59091.16121 Adams 1994 -41193.75363 -82387.50726 Adams 1995 -57186.93091 -114373.8618 Adams 1996 -78821.69682 -157643.3936 Adams 1997 -107203.0308 -214406.0617 Adams 1998 -144686.6027 -289373.2055 Adams 1999 -193074.2313 -386148.4625 Adams 2000 -257022.6462 -514045.2923 Adams 2001 -341873.5824 -683747.1649 Adams 2002 -455768.9238 -911537.8475 Adams 2003 -607328.0552 -1214656.11 Adams 2004 -808423.6442 -1616847.288 Adams 2005 -1075474.604 -2150949.209 Allen 1981 468.7313814 937.4627629 Allen 1982 490.7175253 981.4350507 Allen 1983 522.2108734 1044.421747 Allen 1984 577.0034302 1154.00686 Allen 1985 674.5462361 1349.092472 Allen 1986 816.1476042 1632.295208 Allen 1987 967.4184287 1934.836857 Allen 1988 1146.779498 2293.558996 Allen 1989 1308.530843 2617.061687 Allen 1990 1382.579392 2765.158784 Allen 1991 1437.806471 2875.612942 Allen 1992 1428.017453 2856.034906 Allen 1993 1401.219851 2802.439703 Allen 1994 1068.883896 2137.767793 Allen 1995 542.5802303 1085.160461 Allen 1996 -24.0678335 -48.13566699 Allen 1997 -355.5936444 -711.1872888 Allen 1998 -730.2881363 -1460.576273 Allen 1999 -1177.553273 -2355.106546 Allen 2000 -1793.125099 -3586.250197 Allen 2001 -2712.182518 -5424.365036 Allen 2002 -4045.637628 -8091.275255 Allen 2003 -5952.153357 -11904.30671 Allen 2004 -8215.624829 -16431.24966 Allen 2005 -11338.86714 -22677.73428 Bernie Deitrick wrote: Mike, No need to do it series by series. Try this instead: Sub TryNow() Dim chtDeer As Chart Dim shtData As Worksheet Dim rngData As Range Dim strCounty As String Set shtData = Worksheets("Sheet1") Set rngData = shtData.Range("B2", _ shtData.Range("B65536").End(xlUp)).Resize(, 4) strCounty = shtData.Range("A65536").End(xlUp).Value ' make a chart Set chtDeer = Charts.Add With chtDeer .ChartType = xlXYScatterLines .SetSourceData Source:=rngData, PlotBy:=xlColumns .Location Whe=xlLocationAsNewSheet .HasTitle = True .ChartTitle.Characters.Text = strCounty .Axes(xlCategory, xlPrimary).HasTitle = False .Axes(xlValue, xlPrimary).HasTitle = False .HasLegend = False .Name = strCounty End With End Sub -- HTH, Bernie MS Excel MVP wrote in message ups.com... Bernie - Hey, thanks for trying to help. I guess its not that simple. I got "An Unable to Set the Values of the Series Class" error message. If you have a chance, would you mind looking at the code below and see if you can't figure out how I might tweak it to plot 2 series? The code works great for 1 series. I would greatly appreciate your help. Mike Dim lngRow As Long Dim lngStartRow As Long Dim chtDeer As Chart Dim shtData As Worksheet Dim rngXData As Range Dim rngYData As Range Dim strCounty As String ' Set shtData = Worksheets("Sheet1") lngRow = 2 lngStartRow = 2 Do While shtData.Cells(lngRow, 1) < "" If shtData.Cells(lngRow, 1) < shtData.Cells(lngRow + 1, 1) Then Set rngXData = shtData.Range( _ "B" & lngStartRow & ":B" & lngRow) 'Set rngYData = rngXData.Offset(0, 1) Set rngYData = rngXData.Offset(0, 1).Resize(, 3) strCounty = shtData.Cells(lngRow, 1).Value ' make a chart Set chtDeer = Charts.Add With chtDeer Do While .SeriesCollection.Count 0 .SeriesCollection(1).Delete Loop .ChartType = xlXYScatterLines .PlotBy = xlColumns With .SeriesCollection.NewSeries .XValues = rngXData .Values = rngYData End With .Location Whe=xlLocationAsNewSheet .HasTitle = True .ChartTitle.Characters.Text = strCounty .Axes(xlCategory, xlPrimary).HasTitle = False .Axes(xlValue, xlPrimary).HasTitle = False .HasLegend = False .Name = strCounty End With lngStartRow = lngRow + 1 End If lngRow = lngRow + 1 Loop Set rngXData = Nothing Set rngYData = Nothing Set shtData = Nothing Set chtDeer = Nothing End Sub Bernie Deitrick wrote: Try Set rngYData = rngXData.Offset(0,1).Resize(,3) HTH, Bernie MS Excel MVP wrote in message ups.com... Currently using the following code to grab an adjacent column: Set rngYData = rngXData.Offset(0,1) later on in the program: .XValues=rngXdata .Values=rngYdata The above allows me to plot the following data: County Year Estimate1 a 1981 500 a 1982 650 I would like to be able to add another series (or 2) and plot it. County Year Estimate1 Estimate2 Estimate3 a 1981 500 590 600 a 1982 650 750 800 Can I adapt the above code to allow me to do this? Mike |
|
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Simple VBA Question
Any how, thanks again. One more question - I gather adding the
autofilters was just a nice touch and not essential for charting. IOW - I could take that out (which I wont because I like it and plan to use elsewhere) and all would work fine? Mike Bernie Deitrick wrote: Mike, I find it easier to format the chart or graph manually, then when I need to make a chart, I copy it using the macro, and change the data source, also with the macro. Or you can use macros to do the formatting. It's all a matter of taste, I guess. I think that there is a way to change to default formatting for the chart, but I'm not a chart expert.... HTH, Bernie MS Excel MVP wrote in message oups.com... Bernie - you are terrific! I gather that I need to use the macro recorder to get the graph to look like I want it and then paste the code in the appropriate place. Your help is really appreicated. Thank you! Bernie Deitrick wrote: Mike, Try the macro below. Assumes that your data starts in cell A1 of sheet Sheet1, and is contiguous. HTH, Bernie MS Excel MVP Sub GraphByUniqueCategory() Dim myList() As Variant Dim i As Integer Dim j As Integer Dim myCount As Integer Dim chtDeer As Chart Dim shtData As Worksheet Dim rngData As Range Dim myDataSet As Range Dim strCounty As String myCount = 1 Set shtData = Worksheets("Sheet1") With shtData.Range("A2").CurrentRegion.Columns(1) .AdvancedFilter Action:=xlFilterInPlace, Unique:=True ReDim myList(1 To .SpecialCells(xlCellTypeVisible).Count) With .SpecialCells(xlCellTypeVisible) For j = 1 To .Areas.Count For i = 1 To .Areas(j).Cells.Count myList(myCount) = .Areas(j).Cells(i).Value myCount = myCount + 1 Next i Next j End With ActiveSheet.ShowAllData End With Set myDataSet = shtData.Range("B2").CurrentRegion For i = LBound(myList) + 1 To UBound(myList) MsgBox "Now doing " & myList(i) shtData.Range("A2").AutoFilter Field:=1, Criteria1:=myList(i) Set rngData = Intersect(myDataSet, shtData.Range("B:E").SpecialCells(xlCellTypeVisibl e)) strCounty = shtData.Range("A65536").End(xlUp).Value ' make a chart Set chtDeer = Charts.Add With chtDeer .ChartType = xlXYScatterLines .SetSourceData Source:=rngData, PlotBy:=xlColumns .Location Whe=xlLocationAsNewSheet .HasTitle = True .ChartTitle.Characters.Text = strCounty .Axes(xlCategory, xlPrimary).HasTitle = False .Axes(xlValue, xlPrimary).HasTitle = False .HasLegend = False .Name = strCounty End With Next i shtData.ShowAllData End Sub wrote in message oups.com... Bernie - No luck. I've included a small chunk of data so you can see the problems. The goal is to create one new sheet for each county. The code below creates a sheet for Allen County only. While it is plotting both series, it is plotting data for both counties on the same chart. We are getting closer, nonetheless. County Year Total Test Adams 1981 898.8156935 1797.631387 Adams 1982 813.9700179 1627.940036 Adams 1983 600.085855 1200.17171 Adams 1984 168.0186444 336.0372887 Adams 1985 -419.0474403 -838.0948806 Adams 1986 -1076.684463 -2153.368927 Adams 1987 -2013.038366 -4026.076732 Adams 1988 -3828.642918 -7657.285835 Adams 1989 -6414.945724 -12829.89145 Adams 1990 -9823.533193 -19647.06639 Adams 1991 -14595.55507 -29191.11014 Adams 1992 -20926.46511 -41852.93022 Adams 1993 -29545.58061 -59091.16121 Adams 1994 -41193.75363 -82387.50726 Adams 1995 -57186.93091 -114373.8618 Adams 1996 -78821.69682 -157643.3936 Adams 1997 -107203.0308 -214406.0617 Adams 1998 -144686.6027 -289373.2055 Adams 1999 -193074.2313 -386148.4625 Adams 2000 -257022.6462 -514045.2923 Adams 2001 -341873.5824 -683747.1649 Adams 2002 -455768.9238 -911537.8475 Adams 2003 -607328.0552 -1214656.11 Adams 2004 -808423.6442 -1616847.288 Adams 2005 -1075474.604 -2150949.209 Allen 1981 468.7313814 937.4627629 Allen 1982 490.7175253 981.4350507 Allen 1983 522.2108734 1044.421747 Allen 1984 577.0034302 1154.00686 Allen 1985 674.5462361 1349.092472 Allen 1986 816.1476042 1632.295208 Allen 1987 967.4184287 1934.836857 Allen 1988 1146.779498 2293.558996 Allen 1989 1308.530843 2617.061687 Allen 1990 1382.579392 2765.158784 Allen 1991 1437.806471 2875.612942 Allen 1992 1428.017453 2856.034906 Allen 1993 1401.219851 2802.439703 Allen 1994 1068.883896 2137.767793 Allen 1995 542.5802303 1085.160461 Allen 1996 -24.0678335 -48.13566699 Allen 1997 -355.5936444 -711.1872888 Allen 1998 -730.2881363 -1460.576273 Allen 1999 -1177.553273 -2355.106546 Allen 2000 -1793.125099 -3586.250197 Allen 2001 -2712.182518 -5424.365036 Allen 2002 -4045.637628 -8091.275255 Allen 2003 -5952.153357 -11904.30671 Allen 2004 -8215.624829 -16431.24966 Allen 2005 -11338.86714 -22677.73428 Bernie Deitrick wrote: Mike, No need to do it series by series. Try this instead: Sub TryNow() Dim chtDeer As Chart Dim shtData As Worksheet Dim rngData As Range Dim strCounty As String Set shtData = Worksheets("Sheet1") Set rngData = shtData.Range("B2", _ shtData.Range("B65536").End(xlUp)).Resize(, 4) strCounty = shtData.Range("A65536").End(xlUp).Value ' make a chart Set chtDeer = Charts.Add With chtDeer .ChartType = xlXYScatterLines .SetSourceData Source:=rngData, PlotBy:=xlColumns .Location Whe=xlLocationAsNewSheet .HasTitle = True .ChartTitle.Characters.Text = strCounty .Axes(xlCategory, xlPrimary).HasTitle = False .Axes(xlValue, xlPrimary).HasTitle = False .HasLegend = False .Name = strCounty End With End Sub -- HTH, Bernie MS Excel MVP wrote in message ups.com... Bernie - Hey, thanks for trying to help. I guess its not that simple. I got "An Unable to Set the Values of the Series Class" error message. If you have a chance, would you mind looking at the code below and see if you can't figure out how I might tweak it to plot 2 series? The code works great for 1 series. I would greatly appreciate your help. Mike Dim lngRow As Long Dim lngStartRow As Long Dim chtDeer As Chart Dim shtData As Worksheet Dim rngXData As Range Dim rngYData As Range Dim strCounty As String ' Set shtData = Worksheets("Sheet1") lngRow = 2 lngStartRow = 2 Do While shtData.Cells(lngRow, 1) < "" If shtData.Cells(lngRow, 1) < shtData.Cells(lngRow + 1, 1) Then Set rngXData = shtData.Range( _ "B" & lngStartRow & ":B" & lngRow) 'Set rngYData = rngXData.Offset(0, 1) Set rngYData = rngXData.Offset(0, 1).Resize(, 3) strCounty = shtData.Cells(lngRow, 1).Value ' make a chart Set chtDeer = Charts.Add With chtDeer Do While .SeriesCollection.Count 0 .SeriesCollection(1).Delete Loop .ChartType = xlXYScatterLines .PlotBy = xlColumns With .SeriesCollection.NewSeries .XValues = rngXData .Values = rngYData End With .Location Whe=xlLocationAsNewSheet .HasTitle = True .ChartTitle.Characters.Text = strCounty .Axes(xlCategory, xlPrimary).HasTitle = False .Axes(xlValue, xlPrimary).HasTitle = False .HasLegend = False .Name = strCounty End With lngStartRow = lngRow + 1 End If lngRow = lngRow + 1 Loop Set rngXData = Nothing Set rngYData = Nothing Set shtData = Nothing Set chtDeer = Nothing End Sub Bernie Deitrick wrote: Try Set rngYData = rngXData.Offset(0,1).Resize(,3) HTH, Bernie MS Excel MVP wrote in message ups.com... Currently using the following code to grab an adjacent column: Set rngYData = rngXData.Offset(0,1) later on in the program: .XValues=rngXdata .Values=rngYdata The above allows me to plot the following data: County Year Estimate1 a 1981 500 a 1982 650 I would like to be able to add another series (or 2) and plot it. County Year Estimate1 Estimate2 Estimate3 a 1981 500 590 600 a 1982 650 750 800 Can I adapt the above code to allow me to do this? Mike |
|
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Simple VBA Question
Mike,
No, you can't take the autofilters out - the autofilters aren't just a nice touch, but are actually the key to this macro, as they are what isolate the data for the individual counties. HTH, Bernie MS Excel MVP wrote in message oups.com... Any how, thanks again. One more question - I gather adding the autofilters was just a nice touch and not essential for charting. IOW - I could take that out (which I wont because I like it and plan to use elsewhere) and all would work fine? Mike Bernie Deitrick wrote: Mike, I find it easier to format the chart or graph manually, then when I need to make a chart, I copy it using the macro, and change the data source, also with the macro. Or you can use macros to do the formatting. It's all a matter of taste, I guess. I think that there is a way to change to default formatting for the chart, but I'm not a chart expert.... HTH, Bernie MS Excel MVP wrote in message oups.com... Bernie - you are terrific! I gather that I need to use the macro recorder to get the graph to look like I want it and then paste the code in the appropriate place. Your help is really appreicated. Thank you! Bernie Deitrick wrote: Mike, Try the macro below. Assumes that your data starts in cell A1 of sheet Sheet1, and is contiguous. HTH, Bernie MS Excel MVP Sub GraphByUniqueCategory() Dim myList() As Variant Dim i As Integer Dim j As Integer Dim myCount As Integer Dim chtDeer As Chart Dim shtData As Worksheet Dim rngData As Range Dim myDataSet As Range Dim strCounty As String myCount = 1 Set shtData = Worksheets("Sheet1") With shtData.Range("A2").CurrentRegion.Columns(1) .AdvancedFilter Action:=xlFilterInPlace, Unique:=True ReDim myList(1 To .SpecialCells(xlCellTypeVisible).Count) With .SpecialCells(xlCellTypeVisible) For j = 1 To .Areas.Count For i = 1 To .Areas(j).Cells.Count myList(myCount) = .Areas(j).Cells(i).Value myCount = myCount + 1 Next i Next j End With ActiveSheet.ShowAllData End With Set myDataSet = shtData.Range("B2").CurrentRegion For i = LBound(myList) + 1 To UBound(myList) MsgBox "Now doing " & myList(i) shtData.Range("A2").AutoFilter Field:=1, Criteria1:=myList(i) Set rngData = Intersect(myDataSet, shtData.Range("B:E").SpecialCells(xlCellTypeVisibl e)) strCounty = shtData.Range("A65536").End(xlUp).Value ' make a chart Set chtDeer = Charts.Add With chtDeer .ChartType = xlXYScatterLines .SetSourceData Source:=rngData, PlotBy:=xlColumns .Location Whe=xlLocationAsNewSheet .HasTitle = True .ChartTitle.Characters.Text = strCounty .Axes(xlCategory, xlPrimary).HasTitle = False .Axes(xlValue, xlPrimary).HasTitle = False .HasLegend = False .Name = strCounty End With Next i shtData.ShowAllData End Sub wrote in message oups.com... Bernie - No luck. I've included a small chunk of data so you can see the problems. The goal is to create one new sheet for each county. The code below creates a sheet for Allen County only. While it is plotting both series, it is plotting data for both counties on the same chart. We are getting closer, nonetheless. County Year Total Test Adams 1981 898.8156935 1797.631387 Adams 1982 813.9700179 1627.940036 Adams 1983 600.085855 1200.17171 Adams 1984 168.0186444 336.0372887 Adams 1985 -419.0474403 -838.0948806 Adams 1986 -1076.684463 -2153.368927 Adams 1987 -2013.038366 -4026.076732 Adams 1988 -3828.642918 -7657.285835 Adams 1989 -6414.945724 -12829.89145 Adams 1990 -9823.533193 -19647.06639 Adams 1991 -14595.55507 -29191.11014 Adams 1992 -20926.46511 -41852.93022 Adams 1993 -29545.58061 -59091.16121 Adams 1994 -41193.75363 -82387.50726 Adams 1995 -57186.93091 -114373.8618 Adams 1996 -78821.69682 -157643.3936 Adams 1997 -107203.0308 -214406.0617 Adams 1998 -144686.6027 -289373.2055 Adams 1999 -193074.2313 -386148.4625 Adams 2000 -257022.6462 -514045.2923 Adams 2001 -341873.5824 -683747.1649 Adams 2002 -455768.9238 -911537.8475 Adams 2003 -607328.0552 -1214656.11 Adams 2004 -808423.6442 -1616847.288 Adams 2005 -1075474.604 -2150949.209 Allen 1981 468.7313814 937.4627629 Allen 1982 490.7175253 981.4350507 Allen 1983 522.2108734 1044.421747 Allen 1984 577.0034302 1154.00686 Allen 1985 674.5462361 1349.092472 Allen 1986 816.1476042 1632.295208 Allen 1987 967.4184287 1934.836857 Allen 1988 1146.779498 2293.558996 Allen 1989 1308.530843 2617.061687 Allen 1990 1382.579392 2765.158784 Allen 1991 1437.806471 2875.612942 Allen 1992 1428.017453 2856.034906 Allen 1993 1401.219851 2802.439703 Allen 1994 1068.883896 2137.767793 Allen 1995 542.5802303 1085.160461 Allen 1996 -24.0678335 -48.13566699 Allen 1997 -355.5936444 -711.1872888 Allen 1998 -730.2881363 -1460.576273 Allen 1999 -1177.553273 -2355.106546 Allen 2000 -1793.125099 -3586.250197 Allen 2001 -2712.182518 -5424.365036 Allen 2002 -4045.637628 -8091.275255 Allen 2003 -5952.153357 -11904.30671 Allen 2004 -8215.624829 -16431.24966 Allen 2005 -11338.86714 -22677.73428 Bernie Deitrick wrote: Mike, No need to do it series by series. Try this instead: Sub TryNow() Dim chtDeer As Chart Dim shtData As Worksheet Dim rngData As Range Dim strCounty As String Set shtData = Worksheets("Sheet1") Set rngData = shtData.Range("B2", _ shtData.Range("B65536").End(xlUp)).Resize(, 4) strCounty = shtData.Range("A65536").End(xlUp).Value ' make a chart Set chtDeer = Charts.Add With chtDeer .ChartType = xlXYScatterLines .SetSourceData Source:=rngData, PlotBy:=xlColumns .Location Whe=xlLocationAsNewSheet .HasTitle = True .ChartTitle.Characters.Text = strCounty .Axes(xlCategory, xlPrimary).HasTitle = False .Axes(xlValue, xlPrimary).HasTitle = False .HasLegend = False .Name = strCounty End With End Sub -- HTH, Bernie MS Excel MVP wrote in message ups.com... Bernie - Hey, thanks for trying to help. I guess its not that simple. I got "An Unable to Set the Values of the Series Class" error message. If you have a chance, would you mind looking at the code below and see if you can't figure out how I might tweak it to plot 2 series? The code works great for 1 series. I would greatly appreciate your help. Mike Dim lngRow As Long Dim lngStartRow As Long Dim chtDeer As Chart Dim shtData As Worksheet Dim rngXData As Range Dim rngYData As Range Dim strCounty As String ' Set shtData = Worksheets("Sheet1") lngRow = 2 lngStartRow = 2 Do While shtData.Cells(lngRow, 1) < "" If shtData.Cells(lngRow, 1) < shtData.Cells(lngRow + 1, 1) Then Set rngXData = shtData.Range( _ "B" & lngStartRow & ":B" & lngRow) 'Set rngYData = rngXData.Offset(0, 1) Set rngYData = rngXData.Offset(0, 1).Resize(, 3) strCounty = shtData.Cells(lngRow, 1).Value ' make a chart Set chtDeer = Charts.Add With chtDeer Do While .SeriesCollection.Count 0 .SeriesCollection(1).Delete Loop .ChartType = xlXYScatterLines .PlotBy = xlColumns With .SeriesCollection.NewSeries .XValues = rngXData .Values = rngYData End With .Location Whe=xlLocationAsNewSheet .HasTitle = True .ChartTitle.Characters.Text = strCounty .Axes(xlCategory, xlPrimary).HasTitle = False .Axes(xlValue, xlPrimary).HasTitle = False .HasLegend = False .Name = strCounty End With lngStartRow = lngRow + 1 End If lngRow = lngRow + 1 Loop Set rngXData = Nothing Set rngYData = Nothing Set shtData = Nothing Set chtDeer = Nothing End Sub Bernie Deitrick wrote: Try Set rngYData = rngXData.Offset(0,1).Resize(,3) HTH, Bernie MS Excel MVP wrote in message ups.com... Currently using the following code to grab an adjacent column: Set rngYData = rngXData.Offset(0,1) later on in the program: .XValues=rngXdata .Values=rngYdata The above allows me to plot the following data: County Year Estimate1 a 1981 500 a 1982 650 I would like to be able to add another series (or 2) and plot it. County Year Estimate1 Estimate2 Estimate3 a 1981 500 590 600 a 1982 650 750 800 Can I adapt the above code to allow me to do this? Mike |
|
#11
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Simple VBA Question
Bernie - Looks like I have some homework to do! Thanks again for all
of your help. Mike Bernie Deitrick wrote: Mike, No, you can't take the autofilters out - the autofilters aren't just a nice touch, but are actually the key to this macro, as they are what isolate the data for the individual counties. HTH, Bernie MS Excel MVP wrote in message oups.com... Any how, thanks again. One more question - I gather adding the autofilters was just a nice touch and not essential for charting. IOW - I could take that out (which I wont because I like it and plan to use elsewhere) and all would work fine? Mike Bernie Deitrick wrote: Mike, I find it easier to format the chart or graph manually, then when I need to make a chart, I copy it using the macro, and change the data source, also with the macro. Or you can use macros to do the formatting. It's all a matter of taste, I guess. I think that there is a way to change to default formatting for the chart, but I'm not a chart expert.... HTH, Bernie MS Excel MVP wrote in message oups.com... Bernie - you are terrific! I gather that I need to use the macro recorder to get the graph to look like I want it and then paste the code in the appropriate place. Your help is really appreicated. Thank you! Bernie Deitrick wrote: Mike, Try the macro below. Assumes that your data starts in cell A1 of sheet Sheet1, and is contiguous. HTH, Bernie MS Excel MVP Sub GraphByUniqueCategory() Dim myList() As Variant Dim i As Integer Dim j As Integer Dim myCount As Integer Dim chtDeer As Chart Dim shtData As Worksheet Dim rngData As Range Dim myDataSet As Range Dim strCounty As String myCount = 1 Set shtData = Worksheets("Sheet1") With shtData.Range("A2").CurrentRegion.Columns(1) .AdvancedFilter Action:=xlFilterInPlace, Unique:=True ReDim myList(1 To .SpecialCells(xlCellTypeVisible).Count) With .SpecialCells(xlCellTypeVisible) For j = 1 To .Areas.Count For i = 1 To .Areas(j).Cells.Count myList(myCount) = .Areas(j).Cells(i).Value myCount = myCount + 1 Next i Next j End With ActiveSheet.ShowAllData End With Set myDataSet = shtData.Range("B2").CurrentRegion For i = LBound(myList) + 1 To UBound(myList) MsgBox "Now doing " & myList(i) shtData.Range("A2").AutoFilter Field:=1, Criteria1:=myList(i) Set rngData = Intersect(myDataSet, shtData.Range("B:E").SpecialCells(xlCellTypeVisibl e)) strCounty = shtData.Range("A65536").End(xlUp).Value ' make a chart Set chtDeer = Charts.Add With chtDeer .ChartType = xlXYScatterLines .SetSourceData Source:=rngData, PlotBy:=xlColumns .Location Whe=xlLocationAsNewSheet .HasTitle = True .ChartTitle.Characters.Text = strCounty .Axes(xlCategory, xlPrimary).HasTitle = False .Axes(xlValue, xlPrimary).HasTitle = False .HasLegend = False .Name = strCounty End With Next i shtData.ShowAllData End Sub wrote in message oups.com... Bernie - No luck. I've included a small chunk of data so you can see the problems. The goal is to create one new sheet for each county. The code below creates a sheet for Allen County only. While it is plotting both series, it is plotting data for both counties on the same chart. We are getting closer, nonetheless. County Year Total Test Adams 1981 898.8156935 1797.631387 Adams 1982 813.9700179 1627.940036 Adams 1983 600.085855 1200.17171 Adams 1984 168.0186444 336.0372887 Adams 1985 -419.0474403 -838.0948806 Adams 1986 -1076.684463 -2153.368927 Adams 1987 -2013.038366 -4026.076732 Adams 1988 -3828.642918 -7657.285835 Adams 1989 -6414.945724 -12829.89145 Adams 1990 -9823.533193 -19647.06639 Adams 1991 -14595.55507 -29191.11014 Adams 1992 -20926.46511 -41852.93022 Adams 1993 -29545.58061 -59091.16121 Adams 1994 -41193.75363 -82387.50726 Adams 1995 -57186.93091 -114373.8618 Adams 1996 -78821.69682 -157643.3936 Adams 1997 -107203.0308 -214406.0617 Adams 1998 -144686.6027 -289373.2055 Adams 1999 -193074.2313 -386148.4625 Adams 2000 -257022.6462 -514045.2923 Adams 2001 -341873.5824 -683747.1649 Adams 2002 -455768.9238 -911537.8475 Adams 2003 -607328.0552 -1214656.11 Adams 2004 -808423.6442 -1616847.288 Adams 2005 -1075474.604 -2150949.209 Allen 1981 468.7313814 937.4627629 Allen 1982 490.7175253 981.4350507 Allen 1983 522.2108734 1044.421747 Allen 1984 577.0034302 1154.00686 Allen 1985 674.5462361 1349.092472 Allen 1986 816.1476042 1632.295208 Allen 1987 967.4184287 1934.836857 Allen 1988 1146.779498 2293.558996 Allen 1989 1308.530843 2617.061687 Allen 1990 1382.579392 2765.158784 Allen 1991 1437.806471 2875.612942 Allen 1992 1428.017453 2856.034906 Allen 1993 1401.219851 2802.439703 Allen 1994 1068.883896 2137.767793 Allen 1995 542.5802303 1085.160461 Allen 1996 -24.0678335 -48.13566699 Allen 1997 -355.5936444 -711.1872888 Allen 1998 -730.2881363 -1460.576273 Allen 1999 -1177.553273 -2355.106546 Allen 2000 -1793.125099 -3586.250197 Allen 2001 -2712.182518 -5424.365036 Allen 2002 -4045.637628 -8091.275255 Allen 2003 -5952.153357 -11904.30671 Allen 2004 -8215.624829 -16431.24966 Allen 2005 -11338.86714 -22677.73428 Bernie Deitrick wrote: Mike, No need to do it series by series. Try this instead: Sub TryNow() Dim chtDeer As Chart Dim shtData As Worksheet Dim rngData As Range Dim strCounty As String Set shtData = Worksheets("Sheet1") Set rngData = shtData.Range("B2", _ shtData.Range("B65536").End(xlUp)).Resize(, 4) strCounty = shtData.Range("A65536").End(xlUp).Value ' make a chart Set chtDeer = Charts.Add With chtDeer .ChartType = xlXYScatterLines .SetSourceData Source:=rngData, PlotBy:=xlColumns .Location Whe=xlLocationAsNewSheet .HasTitle = True .ChartTitle.Characters.Text = strCounty .Axes(xlCategory, xlPrimary).HasTitle = False .Axes(xlValue, xlPrimary).HasTitle = False .HasLegend = False .Name = strCounty End With End Sub -- HTH, Bernie MS Excel MVP wrote in message ups.com... Bernie - Hey, thanks for trying to help. I guess its not that simple. I got "An Unable to Set the Values of the Series Class" error message. If you have a chance, would you mind looking at the code below and see if you can't figure out how I might tweak it to plot 2 series? The code works great for 1 series. I would greatly appreciate your help. Mike Dim lngRow As Long Dim lngStartRow As Long Dim chtDeer As Chart Dim shtData As Worksheet Dim rngXData As Range Dim rngYData As Range Dim strCounty As String ' Set shtData = Worksheets("Sheet1") lngRow = 2 lngStartRow = 2 Do While shtData.Cells(lngRow, 1) < "" If shtData.Cells(lngRow, 1) < shtData.Cells(lngRow + 1, 1) Then Set rngXData = shtData.Range( _ "B" & lngStartRow & ":B" & lngRow) 'Set rngYData = rngXData.Offset(0, 1) Set rngYData = rngXData.Offset(0, 1).Resize(, 3) strCounty = shtData.Cells(lngRow, 1).Value ' make a chart Set chtDeer = Charts.Add With chtDeer Do While .SeriesCollection.Count 0 .SeriesCollection(1).Delete Loop .ChartType = xlXYScatterLines .PlotBy = xlColumns With .SeriesCollection.NewSeries .XValues = rngXData .Values = rngYData End With .Location Whe=xlLocationAsNewSheet .HasTitle = True .ChartTitle.Characters.Text = strCounty .Axes(xlCategory, xlPrimary).HasTitle = False .Axes(xlValue, xlPrimary).HasTitle = False .HasLegend = False .Name = strCounty End With lngStartRow = lngRow + 1 End If lngRow = lngRow + 1 Loop Set rngXData = Nothing Set rngYData = Nothing Set shtData = Nothing Set chtDeer = Nothing End Sub Bernie Deitrick wrote: Try Set rngYData = rngXData.Offset(0,1).Resize(,3) HTH, Bernie MS Excel MVP wrote in message ups.com... Currently using the following code to grab an adjacent column: Set rngYData = rngXData.Offset(0,1) later on in the program: .XValues=rngXdata .Values=rngYdata The above allows me to plot the following data: County Year Estimate1 a 1981 500 a 1982 650 I would like to be able to add another series (or 2) and plot it. County Year Estimate1 Estimate2 Estimate3 a 1981 500 590 600 a 1982 650 750 800 Can I adapt the above code to allow me to do this? Mike |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| IF formula-simple question; simple operator | Excel Discussion (Misc queries) | |||
| Simple question | Excel Programming | |||
| Simple Simple Excel usage question | Excel Discussion (Misc queries) | |||
| simple question | Excel Programming | |||
| simple question, hopefully a simple answer! | Excel Programming | |||
Linear Mode
