locked
Modify A Formula With VBA RRS feed

  • Question

  • I have a column that contains subtotals along with other normal data. For example "=SUBTOTAL(1,I14:I18)" I need to change the formula to "=ROUND(SUBTOTAL(1,I14:I18),2)" How can I do this programmatically?

    Thanks,
    Scott

    Tuesday, July 12, 2016 2:51 PM

Answers

  • Sub ReplaceFormulas()
        Dim AllFormulas As Range
        Dim iFormula As Range
        Dim iNewFormula As String
        
        Set AllFormulas = ActiveSheet.UsedRange.SpecialCells(xlCellTypeFormulas)
        
        For Each iFormula In AllFormulas
            iNewFormula = iFormula.Formula
            If Not iNewFormula Like "=SUBTOTAL(*" Then GoTo Continue
            
            iNewFormula = Mid(iNewFormula, 2)
            iNewFormula = "=ROUND(" & iNewFormula & ",2)"
            iFormula.Formula = iNewFormula
    Continue:
        Next iFormula
    End Sub
    


    http://www.ambienteoffice.com.br - http://www.clarian.com.br

    Tuesday, July 12, 2016 5:11 PM
  • Well it's not pretty but this works.

    T.Columns(9).Cells.Replace "=SUBTOTAL(", "ROUND(SUBTOTAL(", xlPart
    T.Columns(9).Cells.Replace ")", "),2)", xlPart
    T.Columns(9).Cells.Replace "ROUND", "=ROUND", xlPart

    Tuesday, July 12, 2016 5:13 PM

All replies

  • A quick search of the web returns some interesting results:

    https://duckduckgo.com/?q=how+to+modify+an+excel+formula+with+vba&t=ha&ia=web

    In fact, I think one of the replies exactly addresses your question. What, is this some kind of class exercise? ;-)

    Good luck.

    Tuesday, July 12, 2016 3:56 PM
  • Thanks for the link. I have looked through some of the posts but have not found what I need yet. This is NOT related to a class exercise.
    Tuesday, July 12, 2016 5:02 PM
  • LOL...keep digging. I saw your EXACT question asked. Perhaps change the query to add in a few more words. Like the program said...'The answer is out there'.

    And sorry if this isn't the answer you want but I see a large part of development being able to do good searches and taking the time to dig just a little bit deeper. It pays off in the long run big time.

    Tuesday, July 12, 2016 5:06 PM
  • Sub ReplaceFormulas()
        Dim AllFormulas As Range
        Dim iFormula As Range
        Dim iNewFormula As String
        
        Set AllFormulas = ActiveSheet.UsedRange.SpecialCells(xlCellTypeFormulas)
        
        For Each iFormula In AllFormulas
            iNewFormula = iFormula.Formula
            If Not iNewFormula Like "=SUBTOTAL(*" Then GoTo Continue
            
            iNewFormula = Mid(iNewFormula, 2)
            iNewFormula = "=ROUND(" & iNewFormula & ",2)"
            iFormula.Formula = iNewFormula
    Continue:
        Next iFormula
    End Sub
    


    http://www.ambienteoffice.com.br - http://www.clarian.com.br

    Tuesday, July 12, 2016 5:11 PM
  • Well it's not pretty but this works.

    T.Columns(9).Cells.Replace "=SUBTOTAL(", "ROUND(SUBTOTAL(", xlPart
    T.Columns(9).Cells.Replace ")", "),2)", xlPart
    T.Columns(9).Cells.Replace "ROUND", "=ROUND", xlPart

    Tuesday, July 12, 2016 5:13 PM