Automatically Increment Cell Once Per Day

Re: Subtract Or Add A Specific Amount Daily

You will have to use the OnTime method!

Re: Subtract Or Add A Specific Amount Daily

'-------------------------------------------------------------
Private Sub Workbook_Open()
'-------------------------------------------------------------
Dim thisDate As Date


    With ThisWorkbook
    
        thisDate = Date - 1 ' in case it doesn't already exist
        On Error Resume Next
        thisDate = Evaluate(.Names("LastOpenDate").RefersTo)
        If thisDate < Date Then
            .Worksheets(1).Range("A1").Value = .Worksheets(1).Range("A1").Value + 100
        End If
        .Names.Add Name:="LastOpenDate", RefersTo:="=" & CLng(Date)
    End With
End Sub

This is workbook event code.
To input this code, right click on the Excel icon on the worksheet
(or next to the File menu if you maximise your workbooks),
select View Code from the menu, and paste the code

Re: Subtract Or Add A Specific Amount Daily

Bob, whilst i understand what you are doing there, there isn't a facility to do it daily without any manual intervention, if the workbook is left open or opened every other day or more the subtraction will cease to be daily!

Re: Subtract Or Add A Specific Amount Daily

True, but who does that.

Some things you have to manage by process. Even Ontime fails if Excel is closed down.

Re: Subtract Or Add A Specific Amount Daily

I agree!

Re: Subtract Or Add A Specific Amount Daily

I thought you might, you MUST be a process man yourself :smile:

Re: Subtract Or Add A Specific Amount Daily

Quote from Jhcorsair

I need to add or subtract a specific amount daily from a sheet with little or no manual intervention.

I'm sure you could be even more vague. Good grief!

Quote

Even Ontime fails if Excel is closed down.

Windows Scheduler

Re: Automatically Increment Cell Once Per Day

VB is not needed. Put =TODAY()-39570 in a cell and format it General. The value in that cell will increase every day.

Re: Automatically Increment Cell Once Per Day

1

Re: Automatically Increment Cell Once Per Day

Quote from Mikerickson

VB is not needed. Put =TODAY()-39570 in a cell and format it General. The value in that cell will increase every day.

Only if the workbook is opened every day or refreshed everyday!

Re: Automatically Increment Cell Once Per Day

Wouldn't any solution (vba/formula) rely on the same?

Quote

By what increment?

Shouldn't YOU know that?

Re: Automatically Increment Cell Once Per Day

Quote from Simon Lloyd

Only if the workbook is opened every day or refreshed everyday!

The workbook need not be opened every day. Every day that it is opened, the number in that cell will have increased at the rate of one per day.
I don't work with closed workbooks, so it may fail in that case. Every time the user sees the cell, it will show the proper value.

K.i.s.s

So simple, yet so very effective. Great thinking Mike