How do I change '2003' in all the formulas in my spreadsheet to '2004?


---
Message posted from http://www.ExcelForum.com/

The easiest method is to use the Find and Replace

SuperJas.

"SuperJas" wrote...
The easiest method is to use the Find and Replace.

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.

EditGo ToSpecialFormulasOK

EditReplace

What: 2003
With: 2004

Replace all.

Gord Dibben Excel MVP

On Wed, 17 Dec 2003 21:20:38 -0600, itsmetisa
wrote:

How do I change '2003' in all the formulas in my spreadsheet to '2004?


---
Message posted from http://www.ExcelForum.com/

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" wrote in message
...
"SuperJas" wrote...
The easiest method is to use the Find and Replace.

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

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

In article ,
says...
How do I change '2003' in all the formulas in my spreadsheet to '2004?


---
Message posted from http://www.ExcelForum.com/