Range.Calculate does not update cell value

[tejay]

Hi all,

I have a cell that I put a date into.
I then make that cell a named range "end_date".

The formula I am using is =year(end_date).

If I use VBA to calculate the cell it won't update.

The only way I can get it to update is click on the cell and press enter.

Any ideas?

Thanks

[Kamboj]

why not you use sheet range in place of named range in vba

Please Login or Register  to view this content.

[joeu2004]

I have a cell that I put a date into. I then make that cell a named range "end_date". The formula I am using is =year(end_date). If I use VBA to calculate the cell it won't update. The only way I can get it to update is click on the cell and press enter. Any ideas?

Which is "the cell" that won't update: the cell called "end_date", or the cell with the formula =YEAR(end_date)?

And how so you "use VBA to calculate" the cell?

Please copy-and-paste the line of code into a response here. Even that might not be sufficient. Please copy-and-paste all relevant lines of code, including declarations and set-up code.

Finally, what is the calculation mode: Automatic or Manual?

The subject line suggests that you might be using a statement of the form Range("a1").Calculate. That updates A1 only if: (1) calculation mode is Manual; or (2) calculation mode is Automatic, and A1 or a cell referenced by the formula A1 has been modified.

If the calculation mode is Automatic, perhaps you should use Range("a1").Dirty. That makes Excel believe A1 has been modified; so it causes A1 and all of its dependent cells (that reference A1) to be recalculated.

[tejay]

Hi All,

Yes it was a manual calculation using range("A1").Calculate

Kamboj - your solution is perfect, thanks heaps!

Thanks all