Okay, you need to do a little looping:


Sub MinMaxPriSec()

Dim Yvals As Variant
Dim nSrs As Long
Dim iSrs As Long

Dim Ymax() As Double
Dim Ymin() As Double

Dim Ymax1 As Double
Dim Ymax2 As Double
Dim Ymin1 As Double
Dim Ymin2 As Double

Dim bPrimary As Boolean
Dim bSecondary As Boolean

Dim sMsg As String

nSrs = ActiveChart.SeriesCollection.Count

ReDim Ymax(1 To nSrs) As Double
ReDim Ymin(1 To nSrs) As Double

For iSrs = 1 To nSrs
Yvals = ActiveChart.SeriesCollection(iSrs).Values

Ymax(iSrs) = WorksheetFunction.Max(Yvals)
Ymin(iSrs) = WorksheetFunction.Min(Yvals)
Next

For iSrs = 1 To nSrs
Select Case ActiveChart.SeriesCollection(iSrs).AxisGroup
Case xlPrimary
If bPrimary Then
If Ymax(iSrs) Ymax1 Then
Ymax1 = Ymax(iSrs)
End If
If Ymin(iSrs) < Ymin1 Then
Ymin1 = Ymin(iSrs)
End If
Else
Ymax1 = Ymax(iSrs)
Ymin1 = Ymin(iSrs)
bPrimary = True
End If
Case xlSecondary
If bSecondary Then
If Ymax(iSrs) Ymax2 Then
Ymax2 = Ymax(iSrs)
End If
If Ymin(iSrs) < Ymin2 Then
Ymin2 = Ymin(iSrs)
End If
Else
Ymax2 = Ymax(iSrs)
Ymin2 = Ymin(iSrs)
bSecondary = True
End If
End Select
Next

If bPrimary Then
sMsg = "Primary Axis:" & vbNewLine
sMsg = sMsg & " Max = " & Ymax1 & vbNewLine
sMsg = sMsg & " Min = " & Ymin1 & vbNewLine
End If
If bSecondary Then
sMsg = sMsg & "Secondary Axis:" & vbNewLine
sMsg = sMsg & " Max = " & Ymax2 & vbNewLine
sMsg = sMsg & " Min = " & Ymin2 & vbNewLine
End If
sMsg = Left$(sMsg, Len(sMsg) - 1)

MsgBox sMsg

End Sub


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


"Patachoup" wrote in message
...
Thx Jon,

I experience a conflict using a TWO vertical axis chart, I want to get the
following to correctly set the scales:

- Maximum value of ALL the series relative to the First Vertical Axis
- Minimum value of ALL the series relative to the Second Vertical Axis

I already use a test like: "If prim_sr.AxisGroup = xlPrimary Then..." to
check the corresponding axis to the serie...but I might not be the right
code
:(

To sum up, I need to code:

For each Chartobject in activesheet
For each serie relative to the first vertical axis
Get the max value
Get the min value
For each serie relative to the second vertical axis
Get the max value
Get the min value



Thx in advance for giving me a hand ;-)


"Jon Peltier" wrote:

Use an approach like this.

Dim Xvals as Variant
Dim Yvals as Variant

Dim Xmax as Double
Dim Xmin as Double
Dim Xmean as Double
Dim Xdev as Double

Xvals = ActiveChart.SeriesCollection(i).XValues
Yvals = ActiveChart.SeriesCollection(i).Values

Xmax = WorksheetFunction.Max(Xvals)
Xmin = WorksheetFunction.Min(Xvals)
Xmean = WorksheetFunction.Average(Xvals)
Xdev = WorksheetFunction.StDev(Xvals)

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


"Patachoup" wrote in message
...
hello,

I am looking forward coding a macro autoscaling a two vertical axis
chart,
with matching major units.

This macro will be linked to the chart by an "OnClick" event

My questions in this context a

- How do you get the maximum and minimum values of a serie from a chart
in
VBA, without referring to any sheet range ?

- How do you calculate the average and std dev of this serie? (to get
the
major unit)

I already had a look at tons of forums and websites you suggested but
nobody
developped that kind of macro, except in AddIns...but I do not want to
use
AddIns ;-)

Thanks in advance