Like I said, it's not the VBA code that causes the problem. I have tried
the same code in a blank spreadsheet, and it does not cause excel to crash.
So I assume it is associated with the size of this spreadsheet, which is not
something I can do anything about. The spreadsheet works perfectly under
manual control, and I do not have the choice of altering the spreadsheet.
Thanks for your interest, though.
This may be some bug that only appears where the spreadsheet takes a long
time to recalculate -my main one takes a few seconds.
If there's anyone around who has come across this sort of problem and/or has
a solution, I would be interested to hear. Thanks again.
"Peter T" <peter_t@discussions wrote in message
...
Any further thoughts? Or do I just shrug and move on?
What I suggested earlier, isolate the line that triggers the crash, post
the
line and anything else relavant. Or if it's OK to keep crashing your Excel
then by all means shrug and move on!
Regards,
Peter T
"GB" wrote in message
...
Hmm, I think it's something about that particular spreadsheet, which is
very
big.
For example, if I set the vba code running, then press ctrl-break, Excel
brings up the msgbox with End and Debug as options, and if I choose
either
one of those, it crashes Excel.
I put the same code into a blank spreadsheet, and follow the same
procedure - it works fine.
This may be some bug that only appears where the spreadsheet takes a long
time to recalculate -my main one takes a few seconds.
Any further thoughts? Or do I just shrug and move on?
"Peter T" <peter_t@discussions wrote in message
...
I couldn't reproduce what I understood as being your problem in Excel 97
with this test
Range("A1").Formula = "=1/0" ' #DIV/0!
Range("B1").Value = Range("A1").Value
v = Range("B1").Value
If IsError(v) Then v = Range("B1").Text ' #DIV/0!
MsgBox v
Could you isolate the particular value that causes the problem and
demonstrate with simplified code, indicate the line that causes the
crash.
Regards,
Peter T
"GB" wrote in message
...
Hi, I have some simple code (below) that works most of the time.
Unfortunately, if there is an error in the data in the spreadsheet,
this
can
cause some of the values in the spreadsheet to go N/A, 'Not a value',
Div/0
or something similar. The result is not just that the macro stops
working,
but it crashes Excel completely. Is this normal expected behaviour
from
Excel 97? What is the best way to trap the errors, so it just keeps
working
and either leaves a blank in my results page or copies the N/A
figures,
then
moves onto the next case?
Can I put in something like:
On Error Resume Next
(You can see that I was brought up on GW-Basic.)
-------------------------------------------------------------------------
-
----------------------
Here is the VBA code:
Public Sub DoAllCalcs()
For JJ = 1 To 740 ' Use 1 for first case, up to no. of cases
Sheets("Basic Info").Range("C1").Value = JJ 'Increments case no. to
get
data
from database
Calculate
Sheets("ResultsList").Range("A" & JJ + 3).Value = JJ
Sheets("ResultsList").Range("B" & JJ + 3).Value =
Sheets("Summary").Range("C4")
Sheets("ResultsList").Range("C" & JJ + 3).Value =
Sheets("Summary").Range("C25")
Sheets("ResultsList").Range("D" & JJ + 3).Value =
Sheets("Summary").Range("D25")
Sheets("ResultsList").Range("E" & JJ + 3).Value =
Sheets("Summary").Range("E25")
Sheets("ResultsList").Range("F" & JJ + 3).Value =
Sheets("Summary").Range("F25")
Sheets("ResultsList").Range("H" & JJ + 3).Value =
Sheets("Summary").Range("G31")
Sheets("ResultsList").Range("I" & JJ + 3).Value =
Sheets("Summary").Range("C35")
Sheets("ResultsList").Range("J" & JJ + 3).Value =
Sheets("PastCalcs").Range("I5")
Sheets("ResultsList").Range("K" & JJ + 3).Value =
Sheets("PastCalcs").Range("Q5")
Debug.Print JJ
Next
End Sub