Prev: How do I make a golf handicapping form in Excel?
Next: excel extract data from one sheet to another
From: Tim on 20 Apr 2010 21:13
Hi All,
xl2003. A candle stick stock chart with some moving averages is located as a
new sheet. Trying to autoscale it using the next macro:
Dim intMax1 As Integer
Dim intMin1 As Integer
Dim intMax2 As Integer
Dim intMin2 As Integer
Dim rngMin As Range
Dim rngMax As Range

Set rngMin = Worksheets("Stock Data").Range("c2:d94")
Set rngMax = Worksheets("Stock Data").Range("c2:d94")

intMin1 = Application.WorksheetFunction.Min(rngMin)
intMin2 = Application.WorksheetFunction.RoundDown(intMin1, 0.1)

intMax1 = Application.WorksheetFunction.Max(rngMax)
intMax2 = Application.WorksheetFunction.RoundUp(intMax1,- 0.1)

ActiveChart.Axes(xlValue, xlSecondary).Select
With ActiveChart.Axes(xlValue, xlSecondary)
.MinimumScale = intMin2
.MaximumScale = intMax2
End With

The macro seems to work when the price of the stock is greater than $20. But
when the stock price is less than $5 the autoscaling macro doesn't work
properly. Tried to add these lines to the above macro:
ActiveChart.Axes(xlValue, xlPrimary).Select
With ActiveChart.Axes(xlValue, xlPrimary)
.MinimumScale = intMin2
.MaximumScale = intMax2
End With

but received “Run-time error '1004' “ message.
Also changed :
intMin2 = Application.WorksheetFunction.RoundDown(intMin1, +0.1)
intMax2 = Application.WorksheetFunction.RoundUp(intMax1, -0.1)
to
intMin2 = Application.WorksheetFunction.RoundDown(intMin1, +0.01)
intMax2 = Application.WorksheetFunction.RoundUp(intMax1, -0.01)
and there wasn't any difference in the resulting chart.
I know how to autoscale the chart when it is located as object in the Data
sheet but this one (when the chart is located as a new sheet) drives me up to
the wall.
Any help is Highly appreciated.

From: Tim on 22 Apr 2010 00:58
Problem solved. Ignore my previous post.
Tim


 | 
Pages: 1
Prev: How do I make a golf handicapping form in Excel?
Next: excel extract data from one sheet to another