CODEWITHSUNDEEP
vbaexcel
calicationoflife
calicationoflife

Reputation: 291

Add yesterday's date to cell

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):

enter image description here

Can anybody help out?

Upvotes: 1

Views: 2149

Answers (5)

Naveenkumar
Naveenkumar

Reputation: 1

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

Upvotes: 0

Vityata
Vityata

Reputation: 43595

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);

Upvotes: 2

Alex P
Alex P

Reputation: 12497

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

Upvotes: 0

sporc
sporc

Reputation: 387

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

Upvotes: 0

calicationoflife
calicationoflife

Reputation: 291

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

Upvotes: 0

Related Questions