How to put these two together?

[Faye]

I have a spreadsheet that I want to produce charts from. I know how to
find the addresses of ranges that I want to produce charts for. And I
have the codes to create a chart for one data range in the spreadsheet.
Now, how do I tie them together so that the charts will be created by
one macro?

Addresses of ranges to chart
===============================
Set rStart = Range("A1")
grp = rStart.Value

i = 2
Do While Cells(i - 1, 1) <> ""
If Cells(i, 1) <> grp Then
Set rng = Range("B1", Cells(i - 1, 4))
MsgBox " Addresses for graphing are: " & rng.Address(0, 0)

Set rStart = Cells(i, 1)
grp = rStart.Value
End If
i = i + 1
Sheets("Chart").Select
Loop


Macro for the chart
==============
Charts.Add
ActiveChart.ChartType = xlLineMarkers
ActiveChart.SetSourceData
Source:=Sheets("Chart").Range(B24), PlotBy:=xlColumns

ActiveChart.SeriesCollection(1).ChartType = xlColumnClustered
ActiveChart.SeriesCollection(1).Name = "=""Maximum"""
ActiveChart.SeriesCollection(2).Name = "=""95th"""
ActiveChart.SeriesCollection(3).Name = "=""5th"""
ActiveChart.SeriesCollection(2).Points(1).ApplyDataLabels
ShowValue:=True
ActiveChart.SeriesCollection(3).Points(1).ApplyDataLabels
ShowValue:=True

With ActiveChart
.HasTitle = True
.ChartTitle.Characters.Text = "Employee Survey"
.Axes(xlCategory, xlPrimary).HasTitle = False
.Axes(xlValue, xlPrimary).HasTitle = False
End With

Thanks in advance.

Faye Larson

 

[Jon Peltier]

Hi Faye -

Your loop simply finds the range in B until it encounters a blank in
column A, and you don't need a loop for that. It looks like you have no
specific category values or labels; did you want to use column A for this?

Sub ChartMyData()
dim iLast as long
iLast = Worksheets("Chart").Range("A1").End(xlDown).Row

Charts.Add
With ActiveChart
.ChartType = xlLineMarkers
.SetSourceData Source:=Worksheets("Chart").Range("B1" & iLast),
PlotBy:=xlColumns

With .SeriesCollection(1)
.ChartType = xlColumnClustered
.Name = "=""Maximum"""
End With

With .SeriesCollection(2)
.Name = "=""95th"""
.Points(1).ApplyDataLabels ShowValue:=True
End With

With .SeriesCollection(3)
.Name = "=""5th"""
.Points(1).ApplyDataLabels ShowValue:=True
End With

.HasTitle = True
.ChartTitle.Characters.Text = "Employee Survey"

'' may not need these two; they're defaults
.Axes(xlCategory, xlPrimary).HasTitle = False
.Axes(xlValue, xlPrimary).HasTitle = False
End With

End Sub

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

 

[Faye]

I need to loop through the data because I need to plot a chart for
every group of value in Column A. For example the following sample data
shows that it should produce 4 charts for each person in Column A, for
the value from B to D.

A B C D
1 FAYE 8965 56342 22969
2 FAYE 78515 332566 122443
3 FAYE 63903 324033 75769
4 FAYE 61027 324078 74577
5 FAYE 60624 323889 100977
6 JEFF 60103 329421 111792
7 JEFF 56807 321219 122607
8 Jean 57618 319549 133422
9 Jean 58433 321294 144237
10 Jean 59119 322247 155052
11 Jean 60971 318629 165867
12 mark 62601 314424 176682
13 mark 65245 318827 187497
14 mark 64662 320495 198312
....
....

I hope I have clarified my question. Your help is greatly appreciated.

Faye Larson

 

[Jon Peltier]

Faye -

Oh, I completely missed the

If Cells(i, 1) <> grp Then

in your first macro. So each person has a chart; each chart shows three
series, for the columns B, C, and D; such that Faye's chart has 5 points,
Jeff's has 2, etc.

You need to loop, as you've done, and as long as the cell in column A is the
same, keep counting. When it changes, make a chart. If it's not zero, reset
the top of the next block of data and continue. Like this, though you may
need to fine tune the series names and data labels.

Sub MakePlots()
Dim rTop As Range
Dim rChart As Range
Dim ChtOb As ChartObject
Dim iRow As Long
Dim dTop As Double, dLeft As Double
Dim dHeight As Double, dWidth As Double

dHeight = 150
dWidth = 250

Set rTop = ActiveSheet.Range("A1")
Set rChart = ActiveSheet.Range("B11")

iRow = 0
Do
iRow = iRow + 1

If ActiveSheet.Range("A" & iRow).Value = rTop.Value Then
' continue adding to chart range
Set rChart = ActiveSheet.Range("B" & rTop.Row & "" & iRow)
Else
' chart what we've collected
dTop = ActiveSheet.Range("A" & rTop.Row).Top
dLeft = ActiveSheet.Range("E1").Left + (rTop.Row - 1) * 5

Set ChtOb = ActiveSheet.ChartObjects.Add(dLeft, dTop, dWidth, dHeight)
With ChtOb.Chart
.ChartType = xlLineMarkers
.SetSourceData Source:=rChart, PlotBy:=xlColumns

With .SeriesCollection(1)
.ChartType = xlColumnClustered
.Name = "=""Maximum"""
End With

With .SeriesCollection(2)
.Name = "=""95th"""
.Points(1).ApplyDataLabels ShowValue:=True
End With

With .SeriesCollection(3)
.Name = "=""5th"""
.Points(1).ApplyDataLabels ShowValue:=True
End With

.HasTitle = True
.ChartTitle.Characters.Text = "Employee Survey - " & rTop.Value
End With

' quit or reset rTop
If Len(ActiveSheet.Range("A" & iRow).Value) = 0 Then Exit Do

Set rTop = ActiveSheet.Range("A" & iRow)
Set rChart = ActiveSheet.Range("B" & iRow & "" & iRow)
End If
Loop

End Sub

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

 

[Faye]

Thanks, Jon. Your code not only provided the solution but also a very
good start for me to learn to code the proper way. Thanks again.

Faye Larson