Replace Subtotal to Sum Formula with VBA Code

[elbarauljr]

Hi everyone,

Is there an excel VBA code that allow you to replace part of a formula after cells selection ?
For example, after selecting multiple cells, select the code to replace SUBTOTAL(9, with SUM(

Some formulas examples:
=SUBTOTAL(9,EA10:EA12) change to =SUM(EA10:EA12)
=A4+SUBTOTAL(9,C20:E53) change to =A4+SUM(C20:E53)
=SUBTOTAL(9,EJ410:EJ412)+SUM(EN91:EN92) change to =SUM(EJ410:EJ412)+SUM(EN91:EN92)

 

[SanjayGMusafir]

after selecting multiple cells, select the code to replace SUBTOTAL(9, with SUM(

You can do that with find and replace - where you can use Look in "Formulas"

First Click Find all then replace all

or replace one by one - as you need or need to skip

 

[Peter_SSs]

Is this what you want?
Test with a copy of your workbook.

Sub Subtotal_To_Sum()
  Selection.Replace What:="SUBTOTAL(9,", Replacement:="SUM(", LookAt:=xlPart
End Sub

 

[elbarauljr]

Yes, the code is easier to use and it works. Thank you!

 

[Peter_SSs]

You're welcome. Thanks for the follow-up.