Using VBA Replace Function to Edit Formulas

[smit3446]

I'm trying to create a loop to Find and Replace parts of formulas on my sheet labeled Output. With my current code, the replace function isn't working and I suspect it's trying to replace string functions only. I need this to alter the formulas. For example here is my array on Sheet1 with column A consisting of old values and a column B consisting of the new values I would like to have in place of column A:

Calc 1999	Calc CY(-18)
Calc 2000	Calc CY(-17)
Calc 2001	Calc CY(-16)
Calc 2002	Calc CY(-15)
Calc 2003	Calc CY(-14)
Calc 2004	Calc CY(-13)
Calc 2005	Calc CY(-12)
Calc 2006	Calc CY(-11)
Calc 2007	Calc CY(-10)
Calc 2008	Calc CY(-9)
Calc 2009	Calc CY(-8)
Calc 2010	Calc CY(-7)
Calc 2011	Calc CY(-6)
Calc 2012	Calc CY(-5)
Calc 2013	Calc CY(-4)
Calc 2014	Calc CY(-3)
Calc 2015	Calc CY(-2)
Calc 2016	Calc CY(-1)
Calc 2017	Calc CY(0)

<tbody>
</tbody>

And here is my code:

Sub FindReplaceAll()

Dim sht As Worksheet
Dim fnd As Variant
Dim rplc As Variant
Dim i As Integer


i = 1


Do While i < 20


fnd = Sheets("Sheet1").Range("A" & i).Value
rplc = Sheets("Sheet1").Range("B" & i).Value


'MsgBox fnd
'MsgBox rplc


  Set sht = Sheets("Output")


   sht.Cells.Replace what:=fnd, Replacement:=rplc, _
    LookAt:=xlWhole, SearchOrder:=xlByRows, MatchCase:=False, _
    SearchFormat:=False, ReplaceFormat:=False


i = i + 1
Loop


End Sub

Any help to get this working to replace text within the formula on the Outputs tab would be very appreciated. Thank you!

 

[Fluff]

Try changing

LookAt:=xlWhole

to

LookAt:=xlPart