Easy charting question (hopefully)
You won't need to set the axis titles every time. So the following could be
simplified. Change:
With .Axes(xlCategory)
.HasTitle = True
.AxisTitle.Characters.Text = "Produce Inventory"
End With
With .Axes(xlValue)
.HasTitle = True
.AxisTitle.Characters.Text = "Tonnes"
.MaximumScale = 1.5 * MaxVal
.MinimumScale = 0
End With
To:
With .Axes(xlValue)
.MaximumScale = 1.5 * MaxVal
.MinimumScale = 0
End With
Greg
"Greg Wilson" wrote:
Perhaps this:
Sub Test()
Dim r As Range, c As Range
Dim cht As Chart
Dim s As Series
Dim ws As Worksheet
Dim coll As Collection
Dim i As Integer
Dim val As Integer, MaxVal As Integer
Set coll = New Collection
Set ws = Sheets("Inventory")
With ws
Set r = .Range(.Cells(2, 1), .Cells(2, 1).End(xlDown))
End With
On Error Resume Next
For Each c In r.Cells
coll.Add c.Value, c.Value
Next
On Error GoTo 0
Set cht = ws.ChartObjects(1).Chart
With cht
For i = 1 To .SeriesCollection.Count
cht.SeriesCollection(1).Delete
Next
For i = 1 To coll.Count
Set s = .SeriesCollection.NewSeries
val = Application.CountIf(r, coll(i))
s.Values = val
MaxVal = IIf(MaxVal < val, val, MaxVal)
s.Name = coll(i)
s.Border.LineStyle = xlNone
s.HasDataLabels = True
With s.Points(1).DataLabel
.Font.Color = vbRed
.Text = coll(i)
End With
Next
With .Axes(xlCategory)
.HasTitle = True
.AxisTitle.Characters.Text = "Produce Inventory"
End With
With .Axes(xlValue)
.HasTitle = True
.AxisTitle.Characters.Text = "Tonnes"
.MaximumScale = 1.5 * MaxVal
.MinimumScale = 0
End With
End With
End Sub
Regards,
Greg
" wrote:
Hi All,
This should be a fairly simple one but I can not find through Google or
Microsoft on how to accomplish it.
My rows of data have 14 columns. One of those columns I would like to
do a count on, so for example the COLUMN of data looks like:
Apples
Apples
Apples
Oranges
Pears
Pears
Zuchini
I want a chart that shows a count for each one, so there would be 3
apples, 1 oranges, 2 pears, and 1 zuchini. This would then be in a bar
chart.
Does that make sense?
Thanks for your help,
SD
|