Refresh multiple Formula Arrays in Worksheet without Control-Shift-Enter on each

[mondorock]

Hi there

I have multiple Formula Arrays in a worksheet that I need to refresh regularly. The manual way is to control-shift-enter each array but this is getting quite tedious.

Does anyone know some vba that I can attach to a button that will refresh all Formula Arrays in a worksheet? I have seen this before so I know it can be done but I can't remember how!

Any help most appreciated

Cheers

Ramon

[mondorock]

Hi again - any takers on my question?

Maybe its too difficult? jk - that will get a response! :P

cheers

Ramon

[romperstomper]

What do you mean by refresh? Are you on manual calculation? If so, you would need to use the Calculate method of the range object in order to only refresh a particular set of formulas.

[DonkeyOte]

If you're running on Auto Calculate the formulae should refresh automatically.

For other methods see: http://www.decisionmodels.com/calcsecretsg.htm

(if you're running on Manual Calculation for sake of performance there's probably some scope for optimisation)

[mondorock]

Ok, thanks for the responses - very fast, much appreciated.

My workbook is on autocalculate, however the Formula Array uses a custom vba function that downloads data from yahoo finance and returns an array of price data - this needs to be refreshed every so often - and currently I achieve that by pressing F2 within the Formula Array and hitting Control-Shift-Enter (note that Shift-F9 worksheet refresh does not work in this instance). I will try doing a range.calculate to see if this works.

cheers

Ramon

[mondorock]

ok, so recalling the function seems to be the way to do it, I just assigned this to a button and replicated for each array:

Range("B5").Select
Selection.FormulaArray = "=smfGetYahooHistory(Ticker,,,,,,,,,,)"

Thanks for your help!

[plunavat]

Press ctrl+alt+f9

Regards,
Pranav C Lunavat