Chart Adjusting Macro II
 

Hi all

I'm using a chart with two Y axes - eg the Left axis (LA) could be MW
of Generation and the Right axes (RA) could be total electric demand.
What I want to do is find a way to adjust the RA - so that it exactly
matches the scaling on the LA . Right now, I have the LA scaled as
follows :

min 0
max 70000
increment 10,000

RA
min 15000
max 60000
step 5000

I have about 10 such charts, which update everyday. Is there anyway
(excel formula or VBA code) to get the RA to "mirror" the LA?

thanks
chet

Chart Adjusting Macro II
 

do you mean a macro to loop through the charts and set the Min, Max and
Increment values?

Turn on the macro recorder and do it manually - then turn off the macro
recorder and look at the recorded code. Then adjust/genralize that code to
achieve your objective including adding a loop to loop through the charts.

--
Regards,
Tom Ogilvy


" wrote:

Hi all

I'm using a chart with two Y axes - eg the Left axis (LA) could be MW
of Generation and the Right axes (RA) could be total electric demand.
What I want to do is find a way to adjust the RA - so that it exactly
matches the scaling on the LA . Right now, I have the LA scaled as
follows :

min 0
max 70000
increment 10,000

RA
min 15000
max 60000
step 5000

I have about 10 such charts, which update everyday. Is there anyway
(excel formula or VBA code) to get the RA to "mirror" the LA?

thanks
chet

Chart Adjusting Macro II
 

Suggestion: Since the primary and secondary axis scales are to be identical,
use a single Y axis with a more generic label, and label the series to
clearly indicate what each represents.

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


wrote in message
ups.com...
Hi all

I'm using a chart with two Y axes - eg the Left axis (LA) could be MW
of Generation and the Right axes (RA) could be total electric demand.
What I want to do is find a way to adjust the RA - so that it exactly
matches the scaling on the LA . Right now, I have the LA scaled as
follows :

min 0
max 70000
increment 10,000

RA
min 15000
max 60000
step 5000

I have about 10 such charts, which update everyday. Is there anyway
(excel formula or VBA code) to get the RA to "mirror" the LA?

thanks
chet

Chart Adjusting Macro II
 

Tom and John

thanks a ton. here's what I wrote and it seems to be working :


__________________________________________________ _______
Sub AlignAxes()

Dim cht As ChartObject
Dim j As Integer
Dim k As Integer

j = 38

For Each cht In ActiveSheet.ChartObjects
cht.Activate

If ActiveChart.ChartTitle.Characters.Text = "Planned Outgs" Then
Exit Sub
End If


With ActiveChart
' .Parent.Parent.Cells(12, 19) = .Axes(xlCategory).MinimumScale
' .Parent.Parent.Cells(13, 19) = .Axes(xlCategory).MaximumScale
.Parent.Parent.Cells(42, j) = .Axes(xlValue).MinimumScale
.Parent.Parent.Cells(43, j) = .Axes(xlValue).MaximumScale
.Axes(xlValue, xlSecondary).MinimumScale =
ActiveSheet.Cells(42, j).Value
.Axes(xlValue, xlSecondary).MaximumScale =
ActiveSheet.Cells(43, j).Value
End With
j = j + 1
Next


End Sub
__________________________________________________ _________

Jon - I borrowed the parent.parent code from one of your postings on
google groups. To answer your question - I can't make both the data
series appear on one axis. This is because the Load/Electricity Demand
is Hourly data and the Generation MWs - is just one "lump sum". If you
folks can provide an email address - I can send you a sample chart/data
to show what I'm dealing with.

From a programming perspective - I guess what I have is not efficient
because I am first storing the max/min values in designated cells, and
then using those to change the scale on the secondary Y axis. I'm sure
there are better ways - but this works for me so far.Macro hasn't blown
up...atleast so far!

many thanks to both of you

best
chet







Jon Peltier wrote:
Suggestion: Since the primary and secondary axis scales are to be identical,
use a single Y axis with a more generic label, and label the series to
clearly indicate what each represents.

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


wrote in message
ups.com...
Hi all

I'm using a chart with two Y axes - eg the Left axis (LA) could be MW
of Generation and the Right axes (RA) could be total electric demand.
What I want to do is find a way to adjust the RA - so that it exactly
matches the scaling on the LA . Right now, I have the LA scaled as
follows :

min 0
max 70000
increment 10,000

RA
min 15000
max 60000
step 5000

I have about 10 such charts, which update everyday. Is there anyway
(excel formula or VBA code) to get the RA to "mirror" the LA?

thanks
chet