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!
 

Excel Facts

Copy formula down without changing references
If you have =SUM(F2:F49) in F50; type Alt+' in F51 to copy =SUM(F2:F49) to F51, leaving the formula in edit mode. Change SUM to COUNT.

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.
 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,164,055
Messages
5,835,157
Members
430,342
Latest member
Sailingexcel

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top