Excel: Change multiple formulae at once?

Question

I've sorted a chunk of data into sub-totaled fields using the Subtotal tool under the Data tab. However, you are only able to choose one formula to apply.

How can I apply a separate formula to one of the columns based on SUBTOTAL(1,RANGE)? So far I have populated it with Subtotal (9,RANGE), but is there a quicker method to select all of these and change the "9" to a "1"?

Answer 1

1. Select the column.
2. Press CTRL+H.
3. Find What: SUBTOTAL(9
4. Replace with: SUBTOTAL(1
5. Click Replace All

Answer 2

Assume your formulae are in the range A1:A5

Open up the VBE by hitting Alt+F11, and enter the following code in a general module of the same workbook

Function GetFormula(Cell As Range) As String
   GetFormula = Cell.Formula
End Function

I have got this code from http://dmcritchie.mvps.org/excel/formula.htm

For instructions on how to insert this code in a module, see the 'Where to put the code' section at http://www.cpearson.com/excel/writingfunctionsinvba.aspx

Now, you can extract the formula. Use this formula in a helper column, say column B

=SUBSTITUTE(GetFormula(A1),"(1,","(9,")

Now copy this formula upto where you need, and copy-paste values only back where required.

You can also use this trick to modify any other formulae you need, so I suggest you keep this snippet of code handy :)