VBA to find and replace

[Dark0Prince]

Sub FindReplaceAll()
'PURPOSE: Find & Replace text/values throughout entire workbook
'SOURCE: www.TheSpreadsheetGuru.com

Dim sht As Worksheet
Dim fnd As Variant
Dim rplc As Variant

fnd = "17-"
rplc = "18-"

For Each sht In ActiveWorkbook.Worksheets
  sht.Cells.Replace what:=fnd, Replacement:=rplc, _
    LookAt:=xlPart, SearchOrder:=xlByRows, MatchCase:=False, _
    SearchFormat:=False, ReplaceFormat:=False
Next sht

End Sub

Is it possible to change this code to find and replace based on what day it is. So for example if it is Monday it will find and replace for the previous workday which would be find "11-" and replace with "12-". Then if it is Tuesday it will get Mondays number.

 

[Joe4]

Try this:

Sub FindReplaceAll()
'PURPOSE: Find & Replace text/values throughout entire workbook
'SOURCE: www.TheSpreadsheetGuru.com

Dim sht As Worksheet
Dim fnd As Variant
Dim rplc As Variant

[COLOR=#ff0000]fnd = Format(WorksheetFunction.WorkDay(Date, -1), "d-")[/COLOR]
[COLOR=#ff0000]rplc = Format(Date, "d-")[/COLOR]

For Each sht In ActiveWorkbook.Worksheets
  sht.Cells.Replace what:=fnd, Replacement:=rplc, _
    LookAt:=xlPart, SearchOrder:=xlByRows, MatchCase:=False, _
    SearchFormat:=False, ReplaceFormat:=False
Next sht

End Sub

 

[Dark0Prince]

perfect thanks! all i needed to make this work for me was change these lines

fnd = Format(WorksheetFunction.WorkDay(Date, -2), "d-")
rplc = Format(WorksheetFunction.WorkDay(Date, -1), "d-")

 

[Joe4]

You are welcome.

Ah, yes. I thought you wanted today and yesterday, and yesterday and the day before!