Refresh formula's

kkjensen

Active Member
Joined
Jun 22, 2004
Messages
352
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!
 

Some videos you may like

Excel Facts

Which came first: VisiCalc or Lotus 1-2-3?
Dan Bricklin and Bob Frankston debuted VisiCalc in 1979 as a Visible Calculator. Lotus 1-2-3 debuted in the early 1980's, from Mitch Kapor.

BrianB

Well-known Member
Joined
Feb 17, 2003
Messages
8,127
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" :biggrin: in Microsoft efforts to save time by not doing unnecessary calculations.

Code:
Application.Volatile
 

kkjensen

Active Member
Joined
Jun 22, 2004
Messages
352
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.

Code:
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,118,328
Messages
5,571,564
Members
412,405
Latest member
DutchMonkey
Top