Automatic calculation when opening XL file

[fabrigatti]

Hello,

I need to automatically calculate the values on a column of an Excel files, as soon as I open it and basing on values manually inserted in boxes of the same worksheet. Where can I start from?

Regards
Fabrizio.

 

[SkipVought]

Hi,

Check out the Workbook_Open event, which fires when the workbook is opened.

Check out the Calculate Method for a specific range.

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[shVBA]

Hello,

With the Workbook Open Event you could try this....

Private Sub Workbook_Open()

Sheets("Sheet1").Range("A:A").Calculate

End Sub
[\code]

Just change the sheet name and range to whatever you need...

 

[fabrigatti]

This command works fine:

ActiveCell.FormulaR1C1 = "=RC[-3]-RC[-4]"

However, how can I complete the formula considering that I have to check if the difference between 2 values is bigger or lower than 30 (minutes) and, basing on this, add 30 minutes or less?
My rows are like this:

A B C D

1 7.42 12.34 13.52 19.40

If C-D<30 then add that value to the formula in the top ("=RC[-3]-RC[-4]"). If C-D>30, just add 30 to the formula.
How can I achieve this?

 

[SkipVought]

Why are you using VBA code? This is a simple spreadsheet formula.

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[fabrigatti]

Yes, you are right!
I used the IF function. If the condition is true, then apply a formula to that cell. If the condition is false, apply another formula.
This worksin the first box only. If I copy the cell contents with the right button, the formulas are substituted with values and the variables disappear.
What should I put in order to keep those formulas?
In the current situation I get the correct value the first time then the next cells get of course the same result.