Results 1 to 5 of 5
  1. #1
    3 Star Lounger
    Join Date
    Apr 2001
    Location
    Warrington, Cheshire
    Posts
    355
    Thanks
    3
    Thanked 0 Times in 0 Posts

    Application Error (Excel97SR2 on NT4sp5)

    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
    Attached Files Attached Files

  2. #2
    Platinum Lounger
    Join Date
    Feb 2001
    Location
    Weert, Limburg, Netherlands
    Posts
    4,812
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Application Error (Excel97SR2 on NT4sp5)

    I got no errors (XL2000) when deleting column A, B, C or D.
    Jan Karel Pieterse
    Microsoft Excel MVP, WMVP
    www.jkp-ads.com
    Professional Office Developers Association

  3. #3
    3 Star Lounger
    Join Date
    Apr 2001
    Location
    Warrington, Cheshire
    Posts
    355
    Thanks
    3
    Thanked 0 Times in 0 Posts

    Re: Application Error (Excel97SR2 on NT4sp5)

    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

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Application Error (Excel97SR2 on NT4sp5)

    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)"
    <font color=blue>Selection.Value = Selection.Value</font color=blue>
    Section.Offset(1, 0).Select

    and so on for the other five.

    Regards,
    Hans

  5. #5
    3 Star Lounger
    Join Date
    Apr 2001
    Location
    Warrington, Cheshire
    Posts
    355
    Thanks
    3
    Thanked 0 Times in 0 Posts

    Re: Application Error (Excel97SR2 on NT4sp5)

    Many thanks Hans. Your solution works fine.
    Alan

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •