Easy charting question (hopefully)

[[email protected]]

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

[Greg Wilson]

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

"[email protected]" 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
>
>

[Greg Wilson]

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
>
> "[email protected]" 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
> >
> >

[Jon Peltier]

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

<[email protected]> wrote in message
news:[email protected]...
> 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
>