Search and Replace Part of Formulas

**alainsi** · 06-23-2009, 11:50 AM

Hi All -

I am working on a Financial Model and have the following problem:

There is a 'Reporting' tab in the model that collects data from different other tabs. - In particular, there is a series of formulas that receives inputs from a tab called "LoB".

Example A:

=IF('LoB'!K20="";J180;'LoB'!K20)

Example B:

='LoB'!K4

I have now added two sceanario-tabs (versions) of the 'LoB' to the model. These tabs are labelled 'Lob1' and 'Lob2'.

Question: How can I replace in all 2'500 formulas above the 'LoB'!-reference with an IF formula that allows the selection of the scenarios.

e.g. how can I change the formula above (Example B) into:

=IF(scenario=1;'LoB1'!K4;'LoB2'!K4)

Main issue: the solution would have to work for Example A (above) as well.

I have tried to solve the problem with a macro that is able to replace all of the 2'500 formulas, but haven't found a solution yet.

Any help would be very much appreciated! - Thank you.

Rgds,
Alain

**Greg M** · 06-24-2009, 07:09 AM

Hi Alain,

I presume that the formulas you want to change are not identical, i.e. they are not ALL "='LoB'!K4", but that they include e.g. "='LoB'!K5", "='LoB'!L7" etc.

If you want to automate this formula change you'll have to give us a bit more information about where the cells which contain these formulas are located - are they all in the same row? the same column? a specific range on the worksheet?

From what you've said in your post, the only thing the formulas to be changed have in common is that they all begin with "='LoB'!", but I presume that there are other formulas which begin with these characters, but which should NOT be changed as part of this automated process.

Can you supply some more information or post a sample of your workbook?

Regards,

Greg M

**alainsi** · 06-26-2009, 04:53 AM

...thank you for your reply Greg!

(i.) you're right, there are maybe five or six different formula-versioin that all contain the reference "='LoB'!XX"

(ii.) location of the cells: the spreadsheet contains an Income Statement that is projected from 2009 to 2019. Therefore I have in column 'B' all the line items (i.e. sales, different costs, income, etc.), in the top row are the years. - a macro would have to screen and replace (where appropriate) all formulas in this matrix.

(iii.) an automated process would have to change ALL formulas that begin with "='LoB'!". There are no exceptions since the LoB sheet in the model will be replaced by three sheets containing LoB scenarios. - i.e. before there was only one case, now we have a BASE- and BEST-CASE scenario.

The scenarios are numbered '1' and '2', so that the "if"-formula that will replace the "='LoB'!XX"-references should have the following syntax:

=IF(scenario=1;'LoB1'!XX;'LoB2'!XX)

(iv.) unfortunately, for confidentiality reasons, I cannot upload the model. It contains sensitive information re a potential M&A transaction.

Again: thank you very much for your help Greg!

Rgds,
Alain.

**Richard Schollar** · 06-26-2009, 05:15 AM

Alain

Is it just the formulas that begin

='LoB'!

or might the 'LoB'! occur later on in the formula as well eg like:

=1000 + 'LoB'!

I do something like this with some formulas in a spreadsheet of mine. I use Regular Expressions to match the pattern and run the changes. It will be relatively straightforward to amend to apply to your situation.

Richard

**alainsi** · 06-26-2009, 10:19 AM

Richard -

the "'LoB'!K44"-Reference would have to be replaced in several different types of formulas. The two most frequent versions are the following:

(i.) ='LoB'!K44

(ii.) =IF('LoB'!K44="";J180;'LoB'!K44)

The (ii.) version I'd guess is the more difficult case where the reference occurs twice in the formula. - i.e. the case you mentioned.

Thank you for your help!

Rgds,
Alain.

**Richard Schollar** · 06-26-2009, 11:34 AM

Alain

Try this on a copy of your workbook - the sheet where you want the formulas changed should be visible in Excel (ie Active) when you run this. It will only amend formulas with

LoB1!XX

in the B column

Sub change_formulas()
Dim r As Range

With CreateObject("vbscript.regexp")
    .Global = True
    .Pattern = "(LoB1\!)(($)?[A-Z]{1,2}($)?[0-9]+)"
        For Each r In Application.Intersect(ActiveSheet.Range("B:B"), ActiveSheet.UsedRange).SpecialCells(xlCellTypeFormulas)
                r.FormulaLocal = .Replace(r.FormulaLocal, "IF(scenario=1;'Lob1'!$2;'LoB2'!$2)")
        Next r
End With
        
End Sub

Richard

**martindwilson** · 06-29-2009, 08:47 AM

what was wrong with find replace?
find
lob! replace
lob1!
lookin in
formulas replace all

**alainsi** · 06-30-2009, 05:48 AM

Martin -

using Ctrl+H would only find and replace "LoB1", it would not extract the cell part of the reference ("K44" in the example above) and past it back into the new formula.

Richard's Macro solves this problem.

Rgds,
Alain