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:
<tbody>
</tbody>
And here is my code:
Any help to get this working to replace text within the formula on the Outputs tab would be very appreciated. Thank you!
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:
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!
Last edited: