Establish the maximum value of both x and y axis in excel ch

Boxer

New Member
Joined
May 15, 2002
Messages
23
Hi

I have a dynamic bubble chart with market share on the x axis and % mkt shr growth on the y axis.

I need to be able to to establish what the max value is on either scale so i can use a formula to calculate where to position an autoshape which indicates the average.

The formula is all worked out, I am just stuck on how I can get the value of the max scale of either axis as the scale changes every time I select a new set of data to look at.

Thanks in anticipation

Boxer
 

Excel Facts

How can you turn a range sideways?
Copy the range. Select a blank cell. Right-click, Paste Special, then choose Transpose.

Damon Ostrander

MrExcel MVP
Joined
Feb 17, 2002
Messages
4,239
Hi Boxer,

If your chart is on a Chart sheet, then you could use:

With Charts("Chart1").Axes(xlValue)
Ymin = .MinimumScale
Ymax = .MaximumScale
End With

where Chart1 is the name of the sheet.

If your chart is embedded on a worksheet, then use:

With ChartObjects("Chart1").Chart.Axes(xlValue)
Ymin = .MinimumScale
Ymax = .MaximumScale
End With

where Chart1 is the name of the ChartObject object (you can view or set the name of the ChartObject object by doing a Shift-left-click on the chart, and the name will appear in the Name Box of the formula bar above cell A1, where it can also be edited).
 

Boxer

New Member
Joined
May 15, 2002
Messages
23
Hi Damon

Thanks fo replying, I think I am being really thick.

I have tried what you said, I have an embedded chart but I keep getting A Compile error, Sub or function not defined which highlights ChartObjects.

Am I being really stupid?

Sub xaxis()

With ChartObjects("Bubble1").Chart.Axes(xlValue)

Ymin = .MinimumScale
Ymax = .MaximumScale
End With

Range("a20").Value = Ymin
Range("a21").Value = Ymax

End Sub
 

Damon Ostrander

MrExcel MVP
Joined
Feb 17, 2002
Messages
4,239
Hi Boxer,

Oops! Sorry, I missed the necessary worksheet qualification. The With line should be

With ActiveSheet.ChartObjects("Bubble1").Chart.Axes(xlValue)

and of couser ActiveSheet could be any worksheet qualifer such as Worksheets("Sheet2")

Damon
 

Forum statistics

Threads
1,147,668
Messages
5,742,514
Members
423,734
Latest member
123hmMission

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top