Geek Answers Handbook

Excel: change multiple formulas at once?

I sorted a piece of data into populated fields using the Total tool on the Data tab. However, you can only select 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 filled it with Subtotal (9, RANGE), but is there a faster way to select all of them and change "9" to "1"?

+7
source share
2 answers
  • Select a column.
  • Press CTRL + H.
  • Find what: SUBTOTAL(9
  • Replace with: SUBTOTAL(1
  • Click Replace All
+12
source

Suppose your formulas are in the range A1: A5

Open VBE by pressing Alt + F11 and enter the following code into the general module of the same book

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

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

For instructions on how to insert this code into the module, see the "Where to Place the Code" section at http://www.cpearson.com/excel/writingfunctionsinvba.aspx

Now you can extract the formula. Use this formula in an auxiliary column, such as column B

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

Now copy this formula to where you need it, and copy the insertion values ​​only where necessary.

You can also use this trick to change any other formulas you need, so I suggest you keep this piece of code handy :)

+1
source

More articles:


All Articles