Find and Replace part of formula using VBA

[chococ]

Hi All,

I am working on a huge spreadsheet and I need to replace part of a formula with something else, without changing the cell reference.

So the original cell contents looks like this: =SUMIFS('New-Traffic'!$L$1:$L$82555, 'New-Traffic'!$C$1:$C$82555,E$5, .......
And I need the new cell contents to look like this: =SUMIFS(INDIRECT("'New-Traffic'!$L$1:$L$"&$D$83),INDIRECT("'New-Traffic'!$C$1:$C$"&$D$83),E$5, ...

Essentially it is replacing all 'New-Traffic'!$?$1:$?$82555 with INDIRECT("'New-Traffic'!$?$1:$?$"&$D$83) if I were to use the find and replace function, but unfortunately Excel's replace function does not support wildcards (but the "find" does). I had a search on the forum and found this: http://www.excelforum.com/excel-prog...-formulas.html

I attempted to modify the macro offered there but I am not too familiar with the backslash and dollar signs' functions in VBA so I am not sure where went wrong (it probably is very wrong right now). Can someone help fix my code please?

Sub change_formulas()
Dim r As Range

With CreateObject("vbscript.regexp")
.Global = True
.Pattern = "('New-Traffic'\!)($$[A-Z]$$1:$$[A-Z]$$82555)"
For Each r In Application.Intersect(ActiveSheet.Range("B:B"), ActiveSheet.UsedRange).SpecialCells(xlCellTypeFormulas)
r.FormulaLocal = .Replace(r.FormulaLocal, "INDIRECT("'New-Traffic'\!)($$$2c$$1:$$$2$$"&$$D$$83)")
Next r
End With

End Sub


Sorry if this is a really stupid question. Any help is much appreciated and thank you very much in advance!

[ragulduy]

can you not do something like:

Please Login or Register  to view this content.

[chococ]

Sorry about the stupid question but do I put the three lines into a macro (between Sub xxx and End Sub) to run it or how do I run them...?

The logic sure does work but when I used the find and replace function to replace 'New-Traffic'! with INDIRECT("'New-Traffic'! it says "The formula you typed contains an error."

Thanks a lot for your help!!

[ragulduy]

Sorry, you can't use the individual replaces directly on the formula as changing one will give you an incorrect formula, you would either need:

Please Login or Register  to view this content.

or as I coded above:

Please Login or Register  to view this content.

and yes you need to put it within a procedure statement (sub...end sub).

Note - I haven't been able to test this as I don't have sumifs in xl2003 but the principle of replacing the start and end of the string should work for you...

[chococ]

Thanks again for your help!! I put the code betweem Sub and End Sub (see below), but it returned "Compile Error: Invalid or unqualified reference", any idea?

Please Login or Register  to view this content.

Also on a side note if I want to specify a range for this replacement to run in, do I just add in a line right after Sub like this:
would this work?

Please Login or Register  to view this content.

Once again thanks so much for the help!!