Mass formula change

Anonymous
2012-07-23T19:55:14+00:00

Hi all,

I need to change several formulas in several protected sheets.

There are the formulas that need to be changed. They are located at Algoma-Working!B25 to Algoma-Working!B30:

=COUNTIFS(Algoma!F3:F299, ">="&TODAY()-7)

=COUNTIFS(Algoma!K3:K2999, ">="&TODAY()-7)

=SUMIF(Algoma!K3:K2999,">="&TODAY()-7,Algoma!I3:I2999)

=SUMIF(Algoma!L3:L2999,">="&TODAY()-7,Algoma!I3:I2999)

=SUMIF(Algoma!N3:N2999,">="&TODAY()-7,Algoma!I3:I2999)

They need to be changed to:

=COUNTIFS(Algoma!F3:F299, ">="&TODAY()-8, Algoma!F3:F299, "<"&TODAY()-2)

=COUNTIFS(Algoma!K3:K2999, ">="&TODAY()-8, Algoma!K3:K2999, "<"&TODAY()-2)

=SUMIFS(Algoma!I3:I2999, Algoma!K3:K2999, ">="&TODAY()-8, Algoma!K3:K2999, "<"&TODAY()-2)

=SUMIFS(Algoma!I3:I2999, Algoma!L3:L2999, ">="&TODAY()-8, Algoma!L3:L2999, "<"&TODAY()-2)

=SUMIFS(Algoma!I3:I2999, Algoma!N3:N2999, ">="&TODAY()-8, Algoma!N3:N2999, "<"&TODAY()-2)

Where it gets complicated is there are 60 other sheets that have the same formula and also need to be changed, but the formula is slightly different in each sheet. They are the same except for "Algoma," which is "Amherst" in the next sheet, for example.

To complicate matters further, the sheets are protected with the password rudolph.

Is there a macro that can make this change for me?

Microsoft 365 and Office | Excel | For home | Windows

Locked Question. This question was migrated from the Microsoft Support Community. You can vote on whether it's helpful, but you can't add comments or replies or follow the question.

0 comments
Answer accepted by question author
Anonymous
2012-07-23T23:09:36+00:00

I believe this will do the tric:

sub FormulaChanger()

dim Formulas(1 to 5) as string,A as variant, RepString as string 

 formulas(1)="= COUNTIFS(Algoma!F3:F299, ">="&TODAY()-8, Algoma!F3:F299, "<"&TODAY()-2)"

 formulas(2)="=COUNTIFS(Algoma!K3:K2999, ">="&TODAY()-8, Algoma!K3:K2999, "<"&TODAY()-2)"

 formulas(3)="=SUMIFS(Algoma!I3:I2999, Algoma!K3:K2999, ">="&TODAY()-8, Algoma!K3:K2999, "<"&TODAY()-2)"

 formulas(4)="=SUMIFS(Algoma!I3:I2999, Algoma!L3:L2999, ">="&TODAY()-8, Algoma!L3:L2999, "<"&TODAY()-2)"

 formulas(5)="=SUMIFS(Algoma!I3:I2999, Algoma!N3:N2999, ">="&TODAY()-8, Algoma!N3:N2999, "<"&TODAY()-2)"

for each a in thisworkbook.sheets

        a.Unprotect ("rudolph")

        repstring=newValue                               ' Link to cell / sheet.name / new value ex: Amherst

        for i=1 to 5:  a.cells(24+i,2).formula=replace(formulas(i),"Algoma",repstring):  next

        a.Protect Password:="rudolph", DrawingObjects:=True, Contents:=True, Scenarios:=False

next

end sub

fix the underlined according to your data, SAVE, then RUN

CHANGES with edit: Fixed formula changes, had missed the added part. Sorry

and some minor changes

Was this answer helpful?

0 comments

1 additional answer

Sort by: Most helpful
Most helpful Newest Oldest
  1. Deleted

    This answer has been deleted due to a violation of our Code of Conduct. The answer was manually reported or identified through automated detection before action was taken. Please refer to our Code of Conduct for more information.

    Comments have been turned off. Learn more