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:

[TABLE="width: 159"]
<tbody>[TR]
[TD]Calc 1999[/TD]
[TD]Calc CY(-18)[/TD]
[/TR]
[TR]
[TD]Calc 2000[/TD]
[TD]Calc CY(-17)[/TD]
[/TR]
[TR]
[TD]Calc 2001[/TD]
[TD]Calc CY(-16)[/TD]
[/TR]
[TR]
[TD]Calc 2002[/TD]
[TD]Calc CY(-15)[/TD]
[/TR]
[TR]
[TD]Calc 2003[/TD]
[TD]Calc CY(-14)[/TD]
[/TR]
[TR]
[TD]Calc 2004[/TD]
[TD]Calc CY(-13)[/TD]
[/TR]
[TR]
[TD]Calc 2005[/TD]
[TD]Calc CY(-12)[/TD]
[/TR]
[TR]
[TD]Calc 2006[/TD]
[TD]Calc CY(-11)[/TD]
[/TR]
[TR]
[TD]Calc 2007[/TD]
[TD]Calc CY(-10)[/TD]
[/TR]
[TR]
[TD]Calc 2008[/TD]
[TD]Calc CY(-9)[/TD]
[/TR]
[TR]
[TD]Calc 2009[/TD]
[TD]Calc CY(-8)[/TD]
[/TR]
[TR]
[TD]Calc 2010[/TD]
[TD]Calc CY(-7)[/TD]
[/TR]
[TR]
[TD]Calc 2011[/TD]
[TD]Calc CY(-6)[/TD]
[/TR]
[TR]
[TD]Calc 2012[/TD]
[TD]Calc CY(-5)[/TD]
[/TR]
[TR]
[TD]Calc 2013[/TD]
[TD]Calc CY(-4)[/TD]
[/TR]
[TR]
[TD]Calc 2014[/TD]
[TD]Calc CY(-3)[/TD]
[/TR]
[TR]
[TD]Calc 2015[/TD]
[TD]Calc CY(-2)[/TD]
[/TR]
[TR]
[TD]Calc 2016[/TD]
[TD]Calc CY(-1)[/TD]
[/TR]
[TR]
[TD]Calc 2017[/TD]
[TD]Calc CY(0)[/TD]
[/TR]
</tbody>[/TABLE]


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