Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

Excel: Change multiple formulae at once?

Tags:

excel

formula

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"?

like image 762
toolshed Avatar asked Mar 26 '12 18:03

toolshed


2 Answers

  1. Select the column.
  2. Press CTRL+H.
  3. Find What: SUBTOTAL(9
  4. Replace with: SUBTOTAL(1
  5. Click Replace All
like image 152
assylias Avatar answered Nov 11 '22 14:11

assylias


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 :)

like image 25
playercharlie Avatar answered Nov 11 '22 15:11

playercharlie