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
|