Microsoft Excel - Refreshing Formulas

Maybe try Application.calculate?

Two options come to mind:

[tab]1) calculation set to manual
Go to Tools > Options > Calculate. Set it Automatic.

FYI: When in Manual, [F9] will force the entire spreadsheet to calculate.

[tab]1) Cells are formatted as text
What do the cells currently show? Do they show the actual formula rather than a value?


[tt]_____
[blue]-John[/blue][/tt]
[tab][red]The plural of anecdote is not data[/red]

Help us help you. Please read FAQ 181-2886 before posting.

BTW, if you need help with the actual macro, please create a new thread for your VBA/Macro-specific question in forum707

[tt]_____
[blue]-John[/blue][/tt]
[tab][red]The plural of anecdote is not data[/red]

Help us help you. Please read FAQ 181-2886 before posting.

I noticed that you stated that Automatic is on.

"I am having to click on each of the cells that I want to update,..."

Calculate each range...


Skip,

_{When a diminutive clarvoyant had disappeared from detention, headlines read...
Small Medium at Large}

Heh. I totally missed that in the OP somehow.

Note to self: must read slower.

[tt]_____
[blue]-John[/blue][/tt]
[tab][red]The plural of anecdote is not data[/red]

Help us help you. Please read FAQ 181-2886 before posting.

and check this out...

Calculations may not occur in an Excel workbook that has many formulas when you use the Calculate method to calculate formulas or after you press SHIFT+F9 to calculate formulas


Skip,

_{When a diminutive clarvoyant had disappeared from detention, headlines read...
Small Medium at Large}

Well cover me with grits and call me breakfast....

I still think it would be helpful to know what the cells are currently displaying, though. Especially since we are dealing with imported data from another application.

A common scenario goes like this:
-Import data
-Insert a column for formulas
-cells just display the formulas rather than the answer

The problem is that the inserted column took on the properties of the column to the left, which was formatted as text. You can select the column, change the format from text to general - or even number or date - but nothing happens.

Pressing F2 (or double clicking into a cell) then pressing Enter works, but you don't want to do that for every single cell.

When dealing with this on the fly, I usually just use the "Times 1 Fix", which I'll outline below.

Another fix is to use Data > Text to columns, fixed width, no breaks, then choose General for the column.

Since you're doing all this in code, you can simply use the following:

Times 1 Fix:
[ul][li]In a cell to the right of all the imported data, type [blue]1[/blue][/li]
[ul][li](this should be right-aligned, indicating that the cell is formatted as a number)[/li][/ul]
[li]copy that cell[/li]
[li]select the cells that are giving you trouble^[red]*[/red][/li]
[li]Go to Edit > Paste Special > Multiply[/li]
[li]Now try applying a new format to those cells [/li][/ul]

Hope something in there helps....

[tt]_____
[blue]-John[/blue][/tt]
[tab][red]The plural of anecdote is not data[/red]

Help us help you. Please read FAQ 181-2886 before posting.