How to update calculations after a #NAME? or #VALUE?i repair

[As-Lag]

Hi

I managed to delete all my named ranges in excel the other week and I am nearly finished locating and renaming the ranges - I had hundreds; fortunately, many are redundant now.

The problem I am finding is that after I have repaired the #NAME?, the #NAME? will not resolve itself until after I have selected the cell and hit return. I am having a similar problem with my VBA functions within the worksheet. These always equate to #VALUE? until I select the cell and hit return.

Is there a way I am able to refresh the sheet without having to select each cell?

Regards Dan

 

[Denial]

I have never had the "opportunity" to investigate this, but might the solution be to select the entire sheet (by clicking in the top left-hand corner of the "frame", then doing a copy and a paste? Just make sure you have a backup copy before you try this.

 

[As-Lag]

I will let you know.

 

[As-Lag]

I did the copy and paste and it did not work. Even managed to gater a few #REF's as well.

 

[Denial]

I'm pretty much out of ideas.[ ] Try automating the process using VBA somehow?[ ] Search for every #NAME? on the sheet, then "select" and "enter" on each of them.[ ] But I am not sure how to do the "search" bit.[ ] Worst case MIGHT be to copy the entire worksheet first, then (on the copy) convert everything to values, then search on this temporary worksheet for the #NAME? instances so you can take the addresses back to the proper worksheet.

 

[IRstuff]

I've seen this effect and I had to brute-force F2-Return each cell.

TTFN (ta ta for now)
I can do absolutely anything. I'm an expert!

https://www.youtube.com/watch?v=BKorP55Aqvg

faq731-376 forum1529 Entire Forum list

http://www.eng-tips.com/forumlist.cfm

 

[SkipVought]

Hi,

But I am not sure how to do the "search" bit.

Check out the Range Find VBA function

https://docs.microsoft.com/en-us/office/vba/api/Excel.Range.Find

Use a loop using Range.FindNext as per the Vb example in the linked document.

Use this code to tickle the cell...

'
            Do 
                [b]c.Value = c.Value[/b]
                Set c = .FindNext(c) 
            Loop While Not c Is Nothing

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]