Running a Macro Daily

[Jenniphurr]

I am needing to track inventory age in column H. So far I have a button that I press first thing in the morning to run this macro. It add +1 to all the numbers that are in column H. My goal is to have my macro do this for me when I open the workbook. The thing is I only want it to perform this macro if the date has changed. Honestly I really just want to not push my age button, plus it takes one more step out of my inventory tracking.

Here is the macro I am working with at the moment. I am even trying to add in some vba code on top of my existing macro.

First off here is my age macro (this is what I link to a button to press each day)

Sub pasteifage()
Dim r As Range, cell As Range
Set r = Range("h:h")
For Each cell In r
If IsNumeric(cell.Value) Then
If cell.Value > 0 Then
cell.Value = cell.Value + 1
End If
Else
MsgBox "Cell " & cell.Address(0, 0) & " does not have a number"
Exit Sub
End If
Next
End Sub


I found this macro and tried to insert it into the above, but sadly no luck getting it to work.

If Sheetx.Range("rLastRun").Value2 < Date Then
<your macro>
Sheetx.Range("rLastRun").Value2 = Now()
End If

 

[cerfani]

If you are adding 1 to a value every morning you simply need to record the start date rounded down to midnight and subtract that from the current date rounded down to midnight (whole number) ... that will give you age in days

=Today-Start

In one column is the Born On Date or whatever you want to call it and the next column can be the age in days...

A2= '1/1/18' (format as date)
B2= '=Today()-A2' (format as number)

 

[Jenniphurr]

I could possibly use this if, I can set that formula to only calculate if there is value within column H. That could work, but I have this page to be fairly dynamic and constantly moving data around. Some inventory does sit around if we are holding for concerns, but everyday new inventory is added and sorted into a different step of my process.


I could input this formula when I transfer new inventory, but this is end up making more steps for myself. Really the macro I have set up works 99% perfect with the only problem being it has to be manually pressed. If I have to add any more steps to this process, I'll just end up continuing to press the button.

 

[cerfani]

=IF(NOT(ISBLANK(H2)),...)

that would execute some function you place in the true result if the corresponging value in column H is not blank

Not sure what you mean by saying that running a macro is more efficient than a formula that just calculates whenever the workbook refreshes.

If you need an age in days... all you need to know is the start date... no need to keep a tally somewhere via macro