Results 1 to 8 of 8
  1. #1
    5 Star Lounger
    Join Date
    Aug 2001
    Location
    Surrey, United Kingdom
    Posts
    1,001
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Good morning

    I have a workbook that has been OK for some 3 years but today for some reason it is not seeing the cell references (and there are many 1000's) in any of the worksheets. For example I have =Setup!$A$301 but it is not showing the result as it should. If I delete it and retype in =Setup!A301 or even retype in =Setup!$A$301 it works OK, so I tried a replace for all of the worksheets to replace $ with nothing and it says it has made 27,000+ changes but then hangs until Excel says it has stopped responding.

    Any pointers would be greatly appreciated otherwise I will be several days (weeks) retying in every single reference.
    Cheers

    Steve

    Asking the questions everbody wants the answers too but feels too stupid to ask themselves :-)

  2. #2
    3 Star Lounger
    Join Date
    May 2008
    Location
    India
    Posts
    306
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Quote Originally Posted by stevehocking View Post
    so I tried a replace for all of the worksheets to replace $ with nothing and it says it has made 27,000+ changes but then hangs until Excel says it has stopped responding.
    I would prefer to let the excel perform the task its own way. Not responding message appears on interruption only.

    BTW, are you trying to replace the contents for all the ws's in one go?
    Regards
    Prasad

  3. #3
    5 Star Lounger
    Join Date
    Aug 2001
    Location
    Surrey, United Kingdom
    Posts
    1,001
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Quote Originally Posted by Prasad View Post
    I would prefer to let the excel perform the task its own way. Not responding message appears on interruption only.

    BTW, are you trying to replace the contents for all the ws's in one go?
    Hi Prasad

    Thanks for the response. I have tried per weeksheet and per workbook to do the replace, whichever I try it says it is has made the replacement but the replace box remains on the screen, I have tried several times and even if I go and leave it for 2 hours+ the replace box is still there and the only way to stop it is to use the'End Task' function (Close or the X does not stop it).

    I have tried the replace per section in worksheets, by worksheet and by workbook with the same results
    Cheers

    Steve

    Asking the questions everbody wants the answers too but feels too stupid to ask themselves :-)

  4. #4
    3 Star Lounger
    Join Date
    May 2008
    Location
    India
    Posts
    306
    Thanks
    0
    Thanked 0 Times in 0 Posts
    See if this could help you.
    Regards
    Prasad

  5. #5
    Platinum Lounger
    Join Date
    Nov 2001
    Location
    Vienna, Wien, Austria
    Posts
    5,009
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Quote Originally Posted by stevehocking View Post
    .....I have a workbook that has been OK for some 3 years but ..... it is not seeing the cell references (and there are many 1000's) in any of the worksheets. ...
    It's beginning to seem to me that perhaps the workbook is starting to become corrupted.

    Was the workbook originally built on Excel 2003?

    Is it a shared workbook?

    Has it, over the years, had various entries from other sources pasted into it (together with the source formatting) from elsewhere? (Often, a simple way to check whether other material has been imported is to use Ctrl + 1 -> Number -> Custom & then scroll down the dialogBox to see how many extra formats have accumulated.)

    If corruption has started, you may need to rebuild it. Rebuilding doesn't mean rekeying in all the entries. It *does* mean copying over all the formulas, formatting & VBA code to a pristine workbook - as well as removing any Links to the sourcing workbook.

    Hope This Helps
    Gre

  6. #6
    5 Star Lounger
    Join Date
    Aug 2001
    Location
    Surrey, United Kingdom
    Posts
    1,001
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Quote Originally Posted by Prasad View Post
    See if this could help you.
    Hi Prasad

    This Macro seems to be a 'find' function and not a replace one? I have run it but it does not seem to do anything

    Thanks anyway
    Cheers

    Steve

    Asking the questions everbody wants the answers too but feels too stupid to ask themselves :-)

  7. #7
    5 Star Lounger
    Join Date
    Aug 2001
    Location
    Surrey, United Kingdom
    Posts
    1,001
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Quote Originally Posted by Malcolm Wagner View Post
    It's beginning to seem to me that perhaps the workbook is starting to become corrupted.

    Was the workbook originally built on Excel 2003?

    Is it a shared workbook?

    Has it, over the years, had various entries from other sources pasted into it (together with the source formatting) from elsewhere? (Often, a simple way to check whether other material has been imported is to use Ctrl + 1 -> Number -> Custom & then scroll down the dialogBox to see how many extra formats have accumulated.)

    If corruption has started, you may need to rebuild it. Rebuilding doesn't mean rekeying in all the entries. It *does* mean copying over all the formulas, formatting & VBA code to a pristine workbook - as well as removing any Links to the sourcing workbook.

    Hope This Helps
    Hi Malcolm

    Thank you for the advice. This workbook was created by me (with help from the Woody's loungers) has no external links or hyperlinks and does not retrieve data from any other sources and receives data input via a combo box from only myself and 2 other users.

    I had already though of this and run several of the tools from the ASAP to remove extra spaces, external links (which there should not be any of) etc. but that does not help.

    In rebuilding, as per your suggestion, is it sufficient to copy and paste each sheet?
    Cheers

    Steve

    Asking the questions everbody wants the answers too but feels too stupid to ask themselves :-)

  8. #8
    Platinum Lounger
    Join Date
    Nov 2001
    Location
    Vienna, Wien, Austria
    Posts
    5,009
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Quote Originally Posted by stevehocking View Post
    ....This workbook was created by me (with help from the Woody's loungers) has no external links or hyperlinks and does not retrieve data from any other sources and receives data input via a combo box from only myself and 2 other users.......

    In rebuilding, as per your suggestion, is it sufficient to copy and paste each sheet?
    Given what you say, the deterioration somewhat surprises me, as it did you.

    If your fellow users are experiencing the same problem, then there should be no harm in trying a rebuild. If they are not, then it could well be something on your own machine.

    Copy|pasting the contents should be fine. As you probably know, any links (back to the old workbook) that arise can be removed by Edit -> Links and then linking to the new workbook.
    Gre

Posting Permissions

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