two vertical axis graph

[Oversteer]

Good evening everyone. In a scatter graph there are two sets of values (from 30 to 100 the first and from 2 to 9 the second). Since the second set of values would be flattened down and not very visible, I decide to move this serie to the secondary axis; I find the first scale to be 0-120 and the second from 0-10. Six division against 5. I'm trying to "extract" the method Excel uses to automatically set the scales to write alternatively: a formula inside the graph options to align scales to the same number of divisions, or a macro that takes the second scale and adapts it to the first so that you have the same number of divisions. If 100 becomes 120, and the first scale becomes 0-140, the second scale should also follow. Any ideas to do it simply? Thank you all in advance. Albert

 

[NdNoviceHlp]

Hi Oversteer. You can adjust this bit of code to suit. HTH. Dave

Dim ChtCnt As Integer
ChtCnt = Sheets("Sheet1").ChartObjects.Count
With Sheets("Sheet1").ChartObjects(ChtCnt).Chart.Axes(xlValue, xlPrimary)
.MajorUnit = ((.MaximumScale - .MinimumScale) / 6)
End With

With Sheets("Sheet1").ChartObjects(ChtCnt).Chart.Axes(xlValue, xlSecondary)
.MajorUnit = ((.MaximumScale - .MinimumScale) / 6)
End With

 

[Oversteer]

Hi Oversteer. You can adjust this bit of code to suit. HTH. Dave

Dim ChtCnt As Integer
ChtCnt = Sheets("Sheet1").ChartObjects.Count
With Sheets("Sheet1").ChartObjects(ChtCnt).Chart.Axes(xlValue, xlPrimary)
.MajorUnit = ((.MaximumScale - .MinimumScale) / 6)
End With

With Sheets("Sheet1").ChartObjects(ChtCnt).Chart.Axes(xlValue, xlSecondary)
.MajorUnit = ((.MaximumScale - .MinimumScale) / 6)
End With

Thank you NdNoviceHlp, I'll try to adapt your code to make sure it can work for every number of divisions and not only 6.
six was just on my example the key point I'm try to make is whichever number of division the first scale gets, the second will always be aligned on the same value

 

[NdNoviceHlp]

You are welcome. You can change the 6 to whatever. A common variable for the 2 axes in place of the 6 seems like it would be best for you. Maybe your first scale /20 would produce your value for the common variable. Dave

 

[Oversteer]

You are welcome. You can change the 6 to whatever. A common variable for the 2 axes in place of the 6 seems like it would be best for you. Maybe your first scale /20 would produce your value for the common variable. Dave

Dave, thanks again; this is the way I'll probably go, even though it's still belonging to the "try and error" category... The best would be if we know the formula used by excel to define the three values MaximumScale, MinimumScale and MajorUnit. For example, does excel first calculate the majorunit with the input values and then round up and round down to get Max and Min, or viceversa? If I could embed the same formula in the macro, then I don't need to go for trials because I could predict the values in advance and cross them with the second set of data being sure they will fit. Does anyone knows these rules?

 

[NdNoviceHlp]

You know what the maximum and minimum values are by the data that is charted and you can control the major unit all with VBA....

With Sheets("Sheet1").ChartObjects(ChtCnt).Chart.Axes(xlValue, xlPrimary)
.MinimumScale = Sheets("Sheet1").Cells(1, 2).Value
.MaximumScale = Sheets("Sheet1").Cells(LastRow, 2).Value
.MajorUnit = ((.MaximumScale - .MinimumScale) / 6)
End With

Where Sheet1 B1 has the minimum data and B & LastRow has the maximum data value. It's very unlikely that anyone will be able to give you the "formula" used by excel to define the values... it's hard to say if MS even knows sometimes. It's better just to directly control these values with VBA as above. HTH. Dave

 

[Oversteer]

it's hard to say if MS even knows sometimes.

Sad but extremely likely... Thank you Dave

 

[NdNoviceHlp]

You are welcome. Thanks for posting your outcome. Dave