mass changes in formulas

  • Thread starter Thread starter itsmetisa
  • Start date Start date
SuperJas said:
The easiest method is to use the Find and Replace.
Click to expand...

Except that finding 2003 and replacing it with 2004 will change numbers like
120034 into 120044 and 1.2003 into 1.2004, as well as changing cell
references like AB2003 into AB2004, which are unlikely to be what the OP
wants to do. There are times when easiest isn't correct.

The following is possibly overkill, but it changes only instances of 2003
that appear as distinct, full tokens. One flaw: it changes tokens inside
string constants. No way to prevent that without writing most of a formula
parser.


Function Subst(orig_text As String, _
match_pat As String, _
replace_pat As String, _
Optional instance As Variant _
) As Variant
'------------------------------------------------------
Dim regex As Object, matches As Object, m As Object

Set regex = CreateObject("vbscript.regexp")
regex.Pattern = match_pat
regex.Global = True

If (IsMissing(instance)) Then
Subst = regex.Replace(orig_text, replace_pat)
ElseIf instance > 0 Then
Set matches = regex.Execute(orig_text)
If instance > matches.Count Then
Subst = orig_text 'matchnum out of bounds - do nothing
Else
Set m = matches.Item(instance - 1)
Subst = Left(orig_text, m.FirstIndex) & _
regex.Replace(m.Value, replace_pat) & _
Right(orig_text, Len(orig_text) - m.FirstIndex - m.Length)
End If
Else
Subst = CVErr(xlErrValue) 'invalid: instance <= 0
End If
End Function


Sub foo()
Dim ws As Worksheet, c As Range, cf As String, nf As Variant

Application.Calculation = xlCalculationManual

For Each ws In ActiveWorkbook.Worksheets
For Each c In ws.UsedRange
If c.HasFormula Then
cf = c.Formula
nf = Subst(cf, "(^|[^.])\b2003\b(?!\.)", "$12004")
If Not IsError(nf) And CStr(nf) <> cf Then
If c.HasArray Then
c.FormulaArray = nf
Else
c.Formula = nf
End If
End If
End If
Next c
Next ws

Application.Calculation = xlCalculationAutomatic
Application.Calculate

End Sub
 
Hit CRTL + A to select all cells on a worksheet.

Edit>Go To>Special>Formulas>OK

Edit>Replace

What: 2003
With: 2004

Replace all.

Gord Dibben Excel MVP
 
Just some added
If you can easily select cells that would not incur these type of incorrect
changes, but only do the correct changes, Edit=>Replace would still be an
option. Replace will work on only the selected cells if you have at least
two cells selected.

--
Regards,
Tom Ogilvy

Harlan Grove said:
SuperJas said:
The easiest method is to use the Find and Replace.
Click to expand...

Except that finding 2003 and replacing it with 2004 will change numbers like
120034 into 120044 and 1.2003 into 1.2004, as well as changing cell
references like AB2003 into AB2004, which are unlikely to be what the OP
wants to do. There are times when easiest isn't correct.

The following is possibly overkill, but it changes only instances of 2003
that appear as distinct, full tokens. One flaw: it changes tokens inside
string constants. No way to prevent that without writing most of a formula
parser.


Function Subst(orig_text As String, _
match_pat As String, _
replace_pat As String, _
Optional instance As Variant _
) As Variant
'------------------------------------------------------
Dim regex As Object, matches As Object, m As Object

Set regex = CreateObject("vbscript.regexp")
regex.Pattern = match_pat
regex.Global = True

If (IsMissing(instance)) Then
Subst = regex.Replace(orig_text, replace_pat)
ElseIf instance > 0 Then
Set matches = regex.Execute(orig_text)
If instance > matches.Count Then
Subst = orig_text 'matchnum out of bounds - do nothing
Else
Set m = matches.Item(instance - 1)
Subst = Left(orig_text, m.FirstIndex) & _
regex.Replace(m.Value, replace_pat) & _
Right(orig_text, Len(orig_text) - m.FirstIndex - m.Length)
End If
Else
Subst = CVErr(xlErrValue) 'invalid: instance <= 0
End If
End Function


Sub foo()
Dim ws As Worksheet, c As Range, cf As String, nf As Variant

Application.Calculation = xlCalculationManual

For Each ws In ActiveWorkbook.Worksheets
For Each c In ws.UsedRange
If c.HasFormula Then
cf = c.Formula
nf = Subst(cf, "(^|[^.])\b2003\b(?!\.)", "$12004")
If Not IsError(nf) And CStr(nf) <> cf Then
If c.HasArray Then
c.FormulaArray = nf
Else
c.Formula = nf
End If
End If
End If
Next c
Next ws

Application.Calculation = xlCalculationAutomatic
Application.Calculate

End Sub
Click to expand...
 
If 2003 is part of a linked file reference, use Edit | Links...

If 2003 is part of a worksheet reference, make a copy of the 2003
worksheet, and rename the original as 2004. XL will update the
references correctly.

If 2003 is used elsewhere, you already have solutions from other
posters.

--
Regards,

Tushar Mehta, MS MVP -- Excel
www.tushar-mehta.com
Excel, PowerPoint, and VBA add-ins, tutorials
Custom MS Office productivity solutions
 
Back
Top