Chart Adjusting Macro II

  • Thread starter Thread starter chesharma
  • Start date Start date
C

chesharma

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
 
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.
 
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
 
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
Click to expand...
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
 
Back
Top