software

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,
SDPerhaps 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(quot;Inventoryquot;)
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 lt; 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 = quot;Produce Inventoryquot;
End With
With .Axes(xlValue)
.HasTitle = True
.AxisTitle.Characters.Text = quot;Tonnesquot;
.MaximumScale = 1.5 * MaxVal
.MinimumScale = 0
End With
End With
End Sub

Regards,
Greg

quot; wrote:

gt; Hi All,
gt;
gt; This should be a fairly simple one but I can not find through Google or
gt; Microsoft on how to accomplish it.
gt;
gt; My rows of data have 14 columns. One of those columns I would like to
gt; do a count on, so for example the COLUMN of data looks like:
gt;
gt; Apples
gt; Apples
gt; Apples
gt; Oranges
gt; Pears
gt; Pears
gt; Zuchini
gt;
gt; I want a chart that shows a count for each one, so there would be 3
gt; apples, 1 oranges, 2 pears, and 1 zuchini. This would then be in a bar
gt; chart.
gt;
gt; Does that make sense?
gt;
gt; Thanks for your help,
gt; SD
gt;
gt;

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 = quot;Produce Inventoryquot;
End With
With .Axes(xlValue)
.HasTitle = True
.AxisTitle.Characters.Text = quot;Tonnesquot;
.MaximumScale = 1.5 * MaxVal
.MinimumScale = 0
End With

To:

With .Axes(xlValue)
.MaximumScale = 1.5 * MaxVal
.MinimumScale = 0
End With

Greg
quot;Greg Wilsonquot; wrote:

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

Put a title on that column, quot;Foodquot;. 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 7You can chart this data.

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

gt; wrote in message oups.com...
gt; Hi All,
gt;
gt; This should be a fairly simple one but I can not find through Google or
gt; Microsoft on how to accomplish it.
gt;
gt; My rows of data have 14 columns. One of those columns I would like to
gt; do a count on, so for example the COLUMN of data looks like:
gt;
gt; Apples
gt; Apples
gt; Apples
gt; Oranges
gt; Pears
gt; Pears
gt; Zuchini
gt;
gt; I want a chart that shows a count for each one, so there would be 3
gt; apples, 1 oranges, 2 pears, and 1 zuchini. This would then be in a bar
gt; chart.
gt;
gt; Does that make sense?
gt;
gt; Thanks for your help,
gt; SD
gt;