Is there any programming tool which can calculate single worksheet

[Gillian]

Using Excel 2007 on Windows 7

I know how to change a formula to a static figure by pressing F9. This is
great for a single cell but is there something that could be used in a range.

For example:

Single Cell - =sum(A1:A3)
Cell shows 51
Select the Cell in the formula bar and press F9 changes the =sum(A1:A3) to 51.

What I would Like to do for a given Month is be able to change formulas to
static figures:

For Example:
for each month there are three calculations. once the month is completed I
would like to stop the month from recalculating as the input cells may change
but only for the future months.

Has anyone come across this before?

 

[Jacob Skaria]

The below code will check whether the date mentioned in cell A2 is less than
current date and if so it will convert the formula cell D2 to its value..Try
the below and feedback.

Select the sheet tab which you want to work with. Right click the sheet tab
and click on 'View Code'. This will launch VBE. Paste the below code to the
right blank portion. Get back to to workbook and try out.

Private Sub Worksheet_Activate()
If Date > CDate(Range("A2")) And Range("A2") > 0 Then _
Range("D2") = Range("D2").Value
End Sub

If this post helps click Yes

 

[David Biddulph]

Copy
Edit/ Paste Special/ Values

 

[Gillian]

Thanks David,

Just what I wanted it to do, You've save me a lot of headaches.