VBA to replace yesterdays date with todays date

[ianmiles2012]

Hello,

I'd like to create VBA code to find yesterday's date and replace it with today's date.
The date is the format YYYYMMDD and is mixed-in with other text in the cell, e.g. ABC YYYYMMDD XYZ

Cells.Replace What:="20130321", Replacement:="20130322", LookAt:=xlPart, _
        SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
        ReplaceFormat:=False

Is there a way to detect YYYYMMDD and replace with YYYYMM(DD+1) ? The replacement date is today's date.

I hope that's clear.
Thanks,
Ian

 

[Andrew Poulsom]

You can use Format(Date-1, "YYYYMMDD") and Format(Date, "YYYYMMDD").

 

[Jonmo1]

I'm assuming the code you posted works based on the hard coded dates...

To make it use Today's date and Yesterday's date, try

Dim TdyDt As String, YstDt As String
TdyDt= Format(Date,"yyyymmdd")
YstDt = Format(Date-1,"yyyymmdd")

Cells.Replace What:=YstDt Replacement:=TdyDt LookAt:=xlPart, _
        SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
        ReplaceFormat:=False

 

[ianmiles2012]

That's fantastic, thanks!

 

[Jonmo1]

Glad to help, thanks for the feedback.