Find and replace text in a formula

Question

I'm having trouble understanding how to make a macro.

I'm trying to search down a column, and in each cell check for the text "TODAY()", and replace it with "TODAY() + B2" where B2 is some number of days.

I've been searching and I think I'm close but I'm still having trouble.

Sub findrep()
    Dim Findtext As String
    Dim Replacetext As String
    Findtext = "TODAY()"
    Replacetext = SUM(TODAY(),"Sheets("Sheet1").Range("B2").Value")
    Columns("A").Replace what:=Findtext, replacement:=Replacetext, lookat:=xlPart, MatchCase:=False

End Sub

I'm wanting to show the date in the cell sometime in the future depending on the number of days in cell B2. The VBA has to be able to search through the formula in the cell as there is more than just "TODAY()" in the formula.

Thanks in advance.

Solution

Try to change this line

Replacetext = SUM(TODAY(),"Sheets("Sheet1").Range("B2").Value")

to

Replacetext = "TODAY()+Sheet1!B2"