Easy charting question (hopefully)
 

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

Easy charting question (hopefully)
 

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

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

Easy charting question (hopefully)
 

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/
_______

wrote in message
oups.com...
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