VBA codes to replace all cells containing a certain value

katherine_nicccc

New Member
Joined
Mar 31, 2024
Messages
2
Office Version
  1. 2016
Platform
  1. Windows
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
 

Excel Facts

Move date out one month or year
Use =EDATE(A2,1) for one month later. Use EDATE(A2,12) for one year later.
Have you tried Ctrl + H (Find and replace; look within formula option)?
 
Upvote 0
@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
Excel Formula:
=SUM('2022:2018'!W6)
and
Excel Formula:
=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.

VBA Code:
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
 
Upvote 0

Forum statistics

Threads
1,223,099
Messages
6,170,107
Members
452,302
Latest member
TaMere

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top