CODEWITHSUNDEEP
vbaexcel
Jamie Walker
Jamie Walker

Reputation: 213

Use Max function in macro to adjust vertical axis of chart

I am trying to use the Max function within a macro to make my charts vertical axis automatically adjust to the highest number in a range of cells. I have started a macro that isn't working but below is kind of the gist of what I'm going for. Any help is much appreciated.

ActiveSheet.ChartObjects(1).Chart.Axes(xlValue).MaximumScale = Application.WorksheetFunction.Max(Range("D26:D100"))

Upvotes: 0

Views: 213

Answers (2)

Shai Rado
Shai Rado

Reputation: 33672

For future cases and good coding practice, try to avoid using ActiveSheet and use referenced objects instead.

Dim Sht As Worksheet
Dim ChtObj  As ChartObject

Set Sht = Worksheets("YourSheetName") '<-- set the worksheet your charts are in
Set ChtObj = Sht.ChartObjects(1) '<-- set the chart object to the specific chart object you want to modify

' now modify the chartobject properties
With ChtObj
    .Chart.Axes(xlValue).MaximumScale = Application.WorksheetFunction.Max(Sht.Range("D26:D100")) '<-- when defining the range, also fully qualify it with the worksheet name
End With

Upvotes: 0

Jamie Walker
Jamie Walker

Reputation: 213

Below is the correct coding VBA Pete helped me come up with, thanks again!

ActiveSheet.ChartObjects(1).Chart.Axes(xlValue).MaximumScale = Application.WorksheetFunction.Max(Range("D26:D100"))

Upvotes: 1

Related Questions