VBA codes to replace all cells containing a certain value

[katherine_nicccc]

Hello VBA experts,

I have a workbook with tons of formula similar to below but with different column and row number.

e.g.
'=SUM('2022!W6, 2021!W6, 2020!W6, 2019!W6, 2018!W6)'
'=SUM('2022!Z6, 2021!Z6, 2020!Z6, 2019!Z6, 2018!Z6)'

And every year, when there is new data e.g. 2023, i have to add in data from 2023 worksheet like below
'=SUM('2023!W6,'2022!W6, 2021!W6, 2020!W6, 2019!W6, 2018!W6)'
'=SUM('2023!W6,'2022!Z6, 2021!Z6, 2020!Z6, 2019!Z6, 2018!Z6)'

This process feels very manual and time consuming. I am wondering if i can use find and replace function to replace this manual process.

I am thinking of vba codes to

• find all the cells with '2022' in their formula
• for each cell found, expand the formula to 2023!xx
  • where xx, which is the row and column number e.g. W6, Z6 and etc, need to taken from the existing cell formula

But i am having trouble designing this vba. Any help appreciated! Thankuuuuu

 

[Cubist]

Have you tried Ctrl + H (Find and replace; look within formula option)?

 

[Snakehips]

@katherine_nicccc
I'm taking a bit of a flyer here. I'm assuming that the single cell reference for each year is constant for any given formula and that 2003!W6 is a typo in your second formula.
Assuming it should be 2023!Z6 ?
If that is correct then I'm thinking that your ever expanding formulas could probably be rationalised.
If your year tabs are consecutive eg left to right ... 2023, 2022,......2018 then your ever expanding formulas above could be converted to sheet array formulas like

=SUM('2022:2018'!W6)

and

=SUM('2022:2018'!Z6)

Then on an annual basis you would just need to edit the leading year tab name.
If that is likely to be acceptable then maybe try testing the below code on a back-up copy of your workbook.

When prompted for the Upper Year to be replaced - enter 2022
When prompted for the new Upper Year - enter 2023
It should then convert your current formulas to the sheet array version. Spanning sheets 2023 to 2018 Maintaining the 2018!XX
Do bear in mind that it will irreversibly change your current formulas.
In subsequent years it could be used to just make the update.
That said, it will convert the upper year back if you enter 2023 and 2022 rather than 2022 and 2023.

Sub Test_Formula_Update()
Dim ws As Worksheet
Dim frng As Range
Dim Newsheet As String
Dim Oldsheet As String
Dim firstFind As String
Dim Newleft As String
Dim l  As Integer
Dim n As Integer
Dim c As Integer

TryAgain:
On Error GoTo TryAgain
Oldsheet = InputBox("Enter the Upper Year sheet name to be replaced")
Newsheet = InputBox("Enter the new Upper Year sheet name")

If Oldsheet = "" Or Newsheet = "" Then GoTo Done:
On Error GoTo Done
Set testsht = Sheets(Trim(Oldsheet))
Set testsht = Sheets(Trim(Newsheet))

Application.ScreenUpdating = False
Newleft = "=SUM('" & Newsheet & ":"

Set ws = ActiveSheet
Set frng = ws.Range("A1")
On Error GoTo Done:
Set frng = ws.Cells.Find(What:="=SUM('" & Oldsheet, After:=ActiveCell, LookIn:= _
        xlFormulas2, lookat:=xlPart, SearchOrder:=xlByRows, SearchDirection:= _
        xlNext, MatchCase:=True)
      
If frng Is Nothing Then GoTo Done
        firstFind = frng.Address
              
        Do While c < 1
            n = InStr(1, frng.Formula2, "2018'!") - 1
            l = Len(frng.Formula2)
            srng = Right(frng.Formula2, l - n)
            frng.Formula2 = Newleft & srng
            Set frng = ws.Cells.FindNext(frng)
            If frng.Address = firstFind Then c = c + 1
        Loop
Done:
On Error GoTo 0
Application.ScreenUpdating = True
End Sub

HTH