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 to total the visible cells?
From the first blank cell below a filtered data set, press Alt+=. Instead of SUM, you will get SUBTOTAL(9,)
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).
 
Upvote 0
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
 
Upvote 0
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
 
Upvote 0

Forum statistics

Threads
1,213,497
Messages
6,113,998
Members
448,541
Latest member
iparraguirre89

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
Back
Top