Add yesterday's date to cell

Question

I have a report which I update every other day. I want to write a formula which updates the first row (which has dates in it) so that it updates the date until yesterday's date.

This is the code I wrote so far:

Sub Update_Newest_Day_Conversions()

    Worksheets("CPC - Conversions DoD").Range("A1").End(xlToRight).Select

    MyDate = Date

    While ActiveCell.Value < MyDate
        ActiveCell.Copy ActiveCell.Offset(0, 1)
        ActiveCell.Offset(0, 1).Select
    Wend

End Sub

Unfortunately I cannot figure out how to add the part where the date is updated by one for every new column. This is how it ends up looking (the loop does not stop obviously because the date remains unchanged):

Can anybody help out?

I suspect that "`Date`" will return current date **and time** and [02/14/2018 00:00] is lower than [02/14/2018 11:17] — Rafalon, Feb 14 '18 at 10:16
exactly, so I want to update it until yesterday's date. Hence "ActiveCell.Value < MyDate" and not "ActiveCell.Value <= MyDate" — calicationoflife, Feb 14 '18 at 10:20
You have to cut the time from your `MyDate` so your comparison is good — Rafalon, Feb 14 '18 at 10:21
ok I got it almost, it was only to add the following line in the loop: ActiveCell.Value = ActiveCell.Value + 1 — calicationoflife, Feb 14 '18 at 10:22
I'm still trying to figure out what you really want to do. Can you show a capture of what was the expected result (as you seemed to solve your problem) ? — Rafalon, Feb 14 '18 at 10:27

Vityata · Answer 1 · 2018-02-14T10:44:24.367

Try this:

Sub UpdateNewestDayConversions()

    Dim selectedRange   As Range
    Dim MyDate          As Date
    Dim myCell          As Range

    With Worksheets("CPC - Conversions DoD")
        Set selectedRange = .Range(.Cells(1, 1), .Cells(1, 1).End(xlToRight))
    End With        

    MyDate = Date - 1

    For Each myCell In selectedRange
        If IsDate(myCell) Then
            If DateSerial(Year(myCell), Month(myCell), Day(myCell)) < MyDate Then
                myCell = DateAdd("d", 1, myCell)
            End If
        Else
            MsgBox "Check " & myCell.Address & " , it is not a valid date!"
        End If
    Next myCell

End Sub

What are the pluses in this code:

it does not use Select;
loops through all the cells in the first row;
checks whether the cell is a valid date and lets you know if this is not the case;
shows you how to use DateAdd(), which is a nice function;
compares only the date of the cell, not the hour and the seconds, with the usage of DateSerial() (see the comment of @Rafalon);

From the question "*which updates the first row*" - from your answer "*all the cells in the first column*" — Rafalon, Feb 14 '18 at 10:43
@Rafalon - do not watch the text in the answer, but the code :) — Vityata, Feb 14 '18 at 10:44

score 0 · Answer 2 · answered Feb 14 '18 at 10:28

I actually got it myself, in case anybody's interested, this is the code:

Sub Update_Newest_Day_Conversions()

Worksheets("CPC - Conversions DoD").Range("A1"). _
End(xlToRight).Select

MyDate = Date - 1

While ActiveCell.Value < MyDate

ActiveCell.Copy ActiveCell.Offset(0, 1)
ActiveCell.Offset(0, 1).Select
ActiveCell.Value = ActiveCell.Value + 1

Wend

End Sub

score 0 · Answer 3 · answered Feb 14 '18 at 10:29

The problem is that you are always copying the same date, hence the date will always be smaller than the current date. You could try something like this:

While ActiveCell.Value < MyDate
    olddate = ActiveCell.Value
    ActiveCell.Offset(0, 1).Select
    ActiveCell.Value = olddate + 1
Wend

Using ActiveCell is not recommend, if you are interested take a look at this: How to avoid using Select in Excel VBA

score 0 · Answer 4 · answered Feb 14 '18 at 10:42

0

Try this:

Sub Update_Newest_Day_Conversions()
    Dim col As Integer

    col = Range("A1").End(xlToRight).Column

    While Cells(1, col) < VBA.Date() - 1
        Cells(1, col).Offset(0, 1) = Cells(1, col) + 1
        col = col + 1
    Wend
End Sub

answered Feb 14 '18 at 10:42

Alex P

12,249
5
51
70

score 0 · Answer 5 · answered Feb 14 '18 at 11:10

Try this

  Sub Update_Newest_Day_Conversions()

   Worksheets("CPC - Conversions DoD").Range("A1").End(xlToRight).Select
   Diff_Date = Date - ActiveCell.Value

   For i = 1 To Diff_Date - 1
       ActiveCell.Offset(0, 1).Value = ActiveCell.Value + 1
       ActiveCell.Offset(0, 1).Select
   Next

  End Sub

Add yesterday's date to cell

5 Answers5