VBA code to set a formula on a range when workbook open

[Rodrigues]

Hi There
I tried to use without success below code to force the formula to set & refreshed when workbook opens
Would like to set a range Sheet2 O6:O10000
Conditional formula:
=IF(L6="","",IF(L6-TODAY(),"Action Needed",IF(L6<TODAY(),"Overdue",IF(L6>TODAY(),""))))

ActiveCell.FormulaR1C1 = _
        "=IF(RC[-3]="""","""",IF(RC[-3]=TODAY(),""Action Needed"",IF(RC[-3]<TODAY(),""Overdue"",IF(RC[-3]>TODAY(),""""))))"
    Range("O6").Select

Any help will be appreciated.
Thanks in advance
Regards
R

 

[p45cal]

try:

Private Sub Workbook_Open()
Sheets("Sheet2").Range("o6:o10000").FormulaR1C1 = "=IF(RC[-3]="""","""",IF(RC[-3]=TODAY(),""Action Needed"",IF(RC[-3]<TODAY(),""Overdue"",IF(RC[-3]>TODAY(),""""))))"
End Sub

in the ThisWorkbook code-module.

But why do you need to do this!?

 

[Rodrigues]

P45cal
The reason I need it, part of the another piece of code is (archive) data move the entire row and past it in a another worksheet and after run the code the cell "o" loose the formula, probably the copycells code is not correct (file attached).
On sheet2 cell o7 the conditional formula is missing, after archive task2 (pasted on sheet3).
How it works, when a task is complete on sheet2 cell "o", key in the complete date and select Archive button.
If I may ask, rather then run the code clicking on the button, could we make it run before save the file? So we have less operator input/mistakes?

Thanks for your help
R

 

[p45cal]

Sure, just put the code you want to run in either the
Workbook_BeforeClose
or
Workbook_BeforeSave
event handlers (in the ThisWorkbook code-module), whichever you think is more appropriate.

 

[Rodrigues]

Hi
Tried that, but the code doesn't work.
On sheet2 cell N7, set a date, so either when before close or save should move/paste the row to sheet3.
Could you please help?
Thanks again
R

 

[p45cal]

You've put code in the right place, but this is a different routine from the subject of this thread.
I can point out that there's an unconditional Exit Sub about 20 lines in which would prevent any later code running (and wouldn't let Application.ScreenUpdating be set to True). Put a Stop instruction near the top of the macro (or a break point) then continue line by line with F8 on the keyboard so you can follow what goes on (it's easier to do this if you have Application.ScreenUpdating set to True).
Beyond that, I don't have time right now to debug your code. Perhaps start a new thread.

 

[Rodrigues]

Thank you so much.
Will have a go.
Regards
R