macro question

[SomethinCool]

I am trying to write a macro where it will write a value to a cell like A7 and then each new day I want it to add a number to the cell. So today it would write a number in A7 and then tomorrow go to A8 automatically. Is this possible?

Thanks.

[DKenny]

Not sure what the question is here.
Are you trying to understand how to

A/ Select the next cell down?
or
B/ Get the code to run everyday?

For A/ you could use the following

Dim rngStartCell As Range
Dim rngNextCell As Range
 
    'Set the starting position
    Set rngStartCell = ThisWorkbook.Worksheets(1).Range("A7")
    
    With rngStartCell
        If .Offset(1, 0).Value = "" Then
            'Special Case handler for when there are
            'no entries belwo the starting cell
            Set rngNextCell = .Offset(1, 0)
        Else
            'All other cases - find the last used cell
            'below the start cell and then select the
            'next cell down
            Set rngNextCell = .End(xlDown).Offset(1, 0)
        End If
    End With

[SomethinCool]

It is sort of B. Lets say today, May 26th it is run and it needs to put data into cell A7. When it is run the next day (May 30th) it will add one more to the previous cell that has data in it. Therefore, it would notice that the day has changed and check the cell A7 to see if it has data and if it does then A7 + 1 = A8 and put new data into that cell.

[DKenny]

OK, so we're part of the way there. Now we just need a mechanism to store the last date that the file was accessed. I would sugget using an Excel Named Rage for this purpose.

Step 1/ Add a named range called LastUsedDate and makes its formula the following, you must inclued the equal sign and the quotes
="4/26/2006"

Step 2/ Add the follwoing procedure to the workbook

Sub CheckLastUsed()
Dim dLastDate As Date
Dim lDaysDiffer
Dim rngStartCell As Range
    
    'Find the last used date
    dLastDate = Evaluate(ThisWorkbook.Names("LastUsedDate").Value)
    
    'Find the number of days since last use
    lDaysDiffer = DateDiff("D", dLastDate, Now())
    
    'Only continue if it is more than one day
    If lDaysDiffer > 1 Then
    
    'Set the starting position
    Set rngStartCell = ThisWorkbook.Worksheets(1).Range("A7")
    
    With rngStartCell
        If .Offset(1, 0).Value = "" Then
            'Special Case handler for when there are
            'no entries below the starting cell
            .Offset(1, 0) = 1
        Else
            'All other cases - find the last used cell
            'below the start cell and then update the
            'next cell down with the next number
            .End(xlDown).Offset(1, 0) = .End(xlDown).Value + 1
        End If
    End With
    
    'Update the named range with todays date
    ThisWorkbook.Names("LastUsedDate").Value = "=""" & Format(Now(), "mm/dd/yyyy") & """"
        
    End If
End Sub

Step 3/ If you need this procedure to run automatically when the workbook opens then add a reference to the proc to the workbook_open event.