Chart Macro Question

[terrytibbs101]

I have created a simple macro to scrape/download share prices from yahoo.

I have created a bog standard candlestick chart and I would like the ability to automatically adjust the y axis scale (price scale) via a macro so that as I download a different stock price the chart automatically adjusts its scale.

The macro I have written is:

Sub chartadj()
'
' chartadj Macro
'


'
ActiveSheet.ChartObjects("Chart 1").Activate
ActiveSheet.ChartObjects("Chart 1").Activate
ActiveSheet.ChartObjects("Chart 1").Activate
ActiveChart.Axes(xlValue).Select
ActiveChart.Axes(xlValue).MinimumScale = 0
ActiveChart.Axes(xlValue).MinimumScale = Parameters!k7
ActiveChart.Axes(xlValue).MaximumScale = 250
ActiveChart.Axes(xlValue).MaximumScale = Parameters!k6
End Sub


But every time I run the macro I get a runtime arror that states "the item with the specified name wasnt found", when I look at the code the below name is highlighted in yellow:
ActiveSheet.ChartObjects("Chart 1").Activate

Clearly its to do with the selection of the chart, I only have one chart in the whole workbook so cant understand why it wont let me do this.

Any thoughts/comments would be very much appreciated?

 

[Domenic]

You'll get that error if the active sheet does not contain a chart named "Chart 1". By the way, let's say that the active sheet does in fact contain a chart named "Chart 1", your code can be rewritten as follows...

Sub chartadj()
    With ActiveSheet.ChartObjects("Chart 1").Chart
        .Axes(xlValue).MinimumScale = Worksheets("Parameters").Range("k7").Value
        .Axes(xlValue).MaximumScale = Worksheets("Parameters").Range("k6").Value
    End With
End Sub

Also, instead of referring to your chart by name, you can also refer to it by index. Therefore, since the chart on your worksheet is the first (and only) chart, you can replace...

With ActiveSheet.ChartObjects("Chart 1").Chart

with

With ActiveSheet.ChartObjects(1).Chart

Hope this helps!