Using VBA code to "wrap" an "If" formula around a large number of existing formulas

[Supasage2003]

Hi guys. I'm trying modify a large number of existing formulas such that they are "wrapped" with a new "If" formula. I was hoping to use VBA code to do this.

Maybe I should explain what I'm trying to accomplish first.... I have two nearly-identical worksheets in my Excel workbook, which contains a financial model for a company. One of the worksheets contains the forecasted financial results for the company (let's call this the forecast sheet). The other worksheet contains the company's actual financial results (let's call this the actual sheet). I would like to create a drop-down menu in row 1 of my forecast sheet that gives me two options, "Forecast" and "Actual". If I select "Actual", the values in that column will pull the data from the actual worksheet. If I select "Forecast", the figures shown will be the result of the formulas that forecast the financials.

I already know how to create the drop-down menu, but can't figure out how to use VBA code to modify my formulas. As an example, I originally had this formula in a cell AS63 in my forecast sheet "=AS20+AS27". After manually modifying the formula for the drop-down list, it now reads "=IF(AS$6="E",AS20+AS27,IF(AS$6="A",Actual!AX68,"No Data"))". I would like to "wrap" this "If" formula around the rest of the formulas in the column, but can't figure out how to do this. Any help would be really appreciated!

 

[Fluff]

Why not just put the new formula in the first cell & just fill down?

 

[Supasage2003]

Why not just put the new formula in the first cell & just fill down?

Hi Fluff. Thanks for the quick reply and the suggestion! Every formula in the column is different (i.e., each row in the column has a different formula), which is why I can't fill it down.

As an aside, I found code that allows bulk modification of formulas (see below). However, that code is set up to add "IFERROR" to all of the formulas. I'm guessing there must be a way to modify this sub to accomplish my objective, but I'm a newbie and haven't been able to figure it out yet.

Sub InsertIFERROR()
Dim R As Range
For Each R In Selection.SpecialCells(xlCellTypeFormulas)
R.Formula = "=IFERROR(" & Mid(R.Formula, 2) & ",""error result"")"
Next R
End Sub

 

[Fluff]

If every single formula is different, then I wouldn't want to risk suggesting anything without knowing exactly what each & every formula was.

 

[Supasage2003]

Most of the formulas are fairly basic. Either adding, subtracting, multiplying or dividing. Nothing complex. Thanks again!

 

[Fluff]

You could try

R.Formula = "=IF(AS$6=""E"","&mid(r.formula,2)&",IF(AS$6=""A"",Actual!AX68,""No Data""))"

But make sure you do it on a copy, as it could well mess everything up.

 

[Supasage2003]

Thanks so much! I'll give it a try!