Easy charting question (hopefully)

  • Thread starter Thread starter aether8203
  • Start date Start date
A

aether8203

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
 
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
 
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
 
Put a title on that column, "Food". Select the range, and from the Data
menu, crate a pivot table. Put the Food field into the Row area, and another
copy of it into the Data area. The result looks like this (hope it pastes
okay):

Count of Food
Food Total
Apples 3
Oranges 1
Pears 2
Zuchini 1
Grand Total 7


You can chart this data.

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Peltier Technical Services
Tutorials and Custom Solutions
http://PeltierTech.com/
_______
 
Back
Top