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

#### Boxer

##### New Member
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

Do you hate GETPIVOTDATA?
Prevent GETPIVOTDATA. Select inside a PivotTable. In the Analyze tab of the ribbon, open the dropown next to Options and turn it off
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).

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

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

Replies
2
Views
1K
Replies
2
Views
4K
Replies
1
Views
2K
Replies
1
Views
4K
Replies
1
Views
2K

1,219,811
Messages
6,150,362
Members
450,955
Latest member
rose8693

### 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.

### Which adblocker are you using?

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

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