My Macro crashes excel - anyone know why.

B

Bruce

I have a macro that does some chart formatting but crashes Excel with the
following error.

"Microsoft Excel for Windows has encountered a problem and needs to close.
We are sorry for the inconvenience"

Very occaisionally it will complete succuessfully (10% of the time), but I
have no idea why it does it. I am running Excel 2000 SP3 and have tried on
other machines with the same result.

Is it poor programming somewhere, see code below;

Bruce

--------------------------------------------------------------------------------------------

Sub myEquityCurve()

Application.ScreenUpdating = False

Dim myArray
Dim myVar1(1 To 4)
Dim myChart(1 To 4)
ReDim myArray(1 To 4)

myEC = "EC"

'Variables
myArray = Array("Sec1", "Sec2", "Sec3", "Sec4")

myVar1(1) = "D10"
myVar1(2) = "F10"
myVar1(3) = "H10"
myVar1(4) = "J10"

myChart(1) = "Chart 1"
myChart(2) = "Chart 3"
myChart(3) = "Chart 4"
myChart(4) = "Chart 5"

'Start Code

Sheets(myEC).Select
Range("A1").Select

k = 1
For Each d In myArray

'Find the row number
c = Format(Sheets("Shares").Range(myVar1(k)), "yyyymmdd")

With Worksheets(d).Range("A1:A1000")
Set a = .Find(c, LookIn:=xlValues)
b = a.Row
mySec = Worksheets(d).Range("B2")
End With

If mySec = "" Then
GoTo Skip
End If

'Update Graph
With Sheets(myEC)
.ChartObjects(myChart(k)).Activate
ActiveChart.ChartTitle.Characters.text = "EC - " & mySec
ActiveChart.SeriesCollection(1).Select
ActiveChart.SeriesCollection(1).XValues =
Sheets(d).Range("I2:I" & b)
ActiveChart.SeriesCollection(1).Values =
Sheets(d).Range("H2:H" & b)

End With

k = k + 1
Skip:
Next d
Range("A1").Select

'Finish
Sheets("Update").Select
Application.ScreenUpdating = False

End Sub

-----------------------------------------------------------------------------------------
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top