Recalculate 1 cell in a macro

[dhstein]

I have calculation set to manual. I'd like to recalculate 1 specific cell,
for example G5 on Sheet "Sales". Can I do something like
Sheets("Sales!G5").Calculate? Thanks for any help on this.

 

[Dave Peterson]

How about:

with worksheets("sales").range("g5")
.formula = .formula
end with

 

[dhstein]

Dave,

Thanks so much - that worked. There are answers like this that leave me
scratching my head - partly out of "Now why does that work?" but mostly out
of "Now how in the world did he know that ?????" Thanks again.

David

 

[Dave Peterson]

I think that this one is like selecting the cell and hitting F2 (to edit it) and
then just hitting enter (with no change).

Excel will think that you changed the formula even if you didn't. So it's
re-evaluation time!

 

[dhstein]

Thanks for the explanation. I kind of knew in the back of my head that Excel
"recalculates" the current cell that you type - but I never gave it much
thought - or thought about how one could apply that property in this
situation. Thanks for a lesson and a clever solution.