Application Error (Excel97SR2 on NT4sp5)

Topic

New Reply

Any help would be much appreciated.
Our predicament:
An excel workbook contains several worksheets. Most sheets contain data entry only.
Final sheet called HistoricalData has onactivate code so that final column is completed with date and array formulas when the worksheet is activated.
The formulas are copied and pasted as data only so as to remove formula from the sheet.
It works OK.
Problem comes when we try to delete a whole column in the middle of this recorded data. An application dialogue appears with the message:
The instruction at “0x3022dcb7” referenced memory at “0x000000010”. The memory could not be written.
etcetera.
Deleting the final column does not have the same effect.
Commenting out the whole vba code and deleting a whole column works fine with no errors.

Any suggestions anyone?
TIA
Alan
Cheshire UK

Reply | Quote

Replies

I got no errors (XL2000) when deleting column A, B, C or D.

Reply | Quote

Sorry Jan, forgot to mention that the error occurs AFTER the code has run. I too had no problem in 97sr2 deleting the columns before the code has run. Unfortunately, in the file I attached, because it has to be less than 100K, I removed the other worksheets so onactivate will have to be run from the vba code and F5 or add a new sheet to jump from.
The resultant column has #NAME? as values but this is just because the original named ranges are not in the sample workbook.
TIA
Alan
Cheshire
UK

Reply | Quote

Hi Alan,

I got the error after inserting a worksheet and activating the HistoricalData sheet. The problem seems to be related to having array formulas and removing them by applying PasteSpecial. Replacing the formulas by their value one by one seems to prevent the problem:

Selection.FormulaArray = “=sum(((numbersystems)=$A” & lngMyRow & “)*1)”
Selection.Value = Selection.Value
Section.Offset(1, 0).Select

and so on for the other five.

Regards,
Hans

Reply | Quote

Many thanks Hans. Your solution works fine.
Alan

Reply | Quote