Refresh formula's

[kkjensen]

I've got a file that someone else put together using a lot of custom functions and external links. Sometimes I get errors back from the function cells until I select them and hit F2->Enter and then they seem to recalculate properly. Just hitting F9 doesn't solve this.

I think the only way I'm going to not lose my mind from "Select cell->F2->Enter"ing every cell in a large table is to have a small macro that would do the equivalent. I don't know if every cell would need to be done...just the ones that return text with "not open" in it somewhere (the error says "File such'n'such not opened" but is sometimes slightly different).

This is beyond my macro-recording abilities. HELP!

 

[BrianB]

It may help to go through the custom functions and put this as the first line of each. This forces them to recalculate every time the rest recalculate. This is "A Feature, Not A Bug" in Microsoft efforts to save time by not doing unnecessary calculations.

Application.Volatile

 

[kkjensen]

Cool, thanks for the tip. BUT....

I wish the guy who wrote them would have known that before he locked the VB...I can't modify a thing.

I've figured out how to do the F2->Enter part of it but it only works on one cell.

Sub Macro2()
    temp = ActiveCell.FormulaR1C1
    ActiveCell.FormulaR1C1 = temp
    ActiveCell.Offset(1, 0).Range("A1").Select
End Sub

The problem I have now is that it only works on the activecell. I need a way to get it to run on all the cells in the worksheet.