Auto adjust Y-axis for chart with 2

[jaydywan]

Someone posted the following, to automatically adjust the Y-axis of an
excel chart depending on the data range. This works for charts with
one axis, but does someone know the code to apply this when a chart
has 2?
------
Hi All Excel-VBA Experts,

I want to write a macro which would automatically adjust the Y-axis of
an excel chart depending on the data range..

Basically I have a chart pointing to a data range. The data range
often changes but the chart doesn't rescale itself to adjust for the
maximum resolution .. The excel auto option doesn't seem to be that
effective..

One way i could think of was to scan for the min & max values in the
data range but dont know how to adjust the chart axes then...

Any help would be greatly appreciated.
Thanks.
-Sunil

----

Hi

see in the PDF file (sample chapter) of chapter 15 of

Professional Excel Development

Written by Stephen Bullen, Rob Bovey and John Green

http://www.oaltd.co.uk/ProExcelDev/Default.htm

It's on one of the last pages, if I recall well. Nice stuff, I use it
too in
one of my private Excel files.

 

[Toyin.Butler]

To set the Maximum scale, find the highest value in your series - e.g
dblIntMax in my code below. Then use a function like the one below to
ascertain the scale.

ActiveSheet.ChartObjects("Chart 1").MaximumScale =
IntRoundUp(dblIntMax)

Function IntRoundUp(zRate)
Dim zDecimal As Single, zRound As Single

zDecimal = zRate - Int(zRate)

Select Case zDecimal
Case Is < 0.25
zRound = 0.25
Case Is < 0.5
zRound = 0.5
Case Is < 0.75
zRound = 0.75
Case Is < 1
zRound = 1
End Select

IntRoundUp = Int(zRate) + zRound

End Function

It depends on the increments you are looking for, this example deals
with interest rates and so the rounding is done on 0.25 basis.

hth

Toyin.

 

[Jon Peltier]

It's the same answer, except you need to determine the min and max on each
axis,compute two sets of scales, and apply them to the two axes.

- Jon