Add "AutoSum" formula to cells in Range based on Cell Text

  • Aloha,

    I have a formula that I found that simulates the AutoSum button in VBA. ActiveCell.Formula = "=Sum(" & Range(ActiveCell.Offset(-1, 0), ActiveCell.Offset(-1, 0).End(xlUp)).Address & ")"
    and I would like a vba code to add this formula or perform the autosum function in each cell in the range that contains the word "sum".

    I'm not sure how to write the macro to do this. Any help is appreciated.

  • Re: Add "AutoSum" formula to cells in Range based on Cell Text


    I think I figured it out myself. But would love to know if there is a better to perform this function as I'm trying to teach myself VBA.

    'change sum in subtotal to formula
    Set sumrange = Range("H:Q")
    For Each Cell In sumrange
    If Cell.value = "sum" Then
    ActiveCell.Formula = "=Sum(" & Range(ActiveCell.Offset(-1, 0), ActiveCell.Offset(-1, 0).End(xlUp)).Address & ")"
    End If
  • Re: Add "AutoSum" formula to cells in Range based on Cell Text

    You don't need to select the cell...

    If Cell.value = "sum" Then 
            Cell.Formula = "=Sum(" & Range(Cell.Offset(-1, 0), Cell.Offset(-1, 0).End(xlUp)).Address & ")" 
        End If

    That'll speed the processing marginally as 1 line of code can be eliminated for each iteration of the loop and is considered 'Good Programming Practise'. There's rarely any need to 'Select' a cell or Range when using VBA.

    You can also speed it up by limiting the number of cells you process. This can be done in a couple of ways. The simplest is using SpecialCells to only query the cells that actually contain some values (whether text or numeric)

    For Each Cell In SumRange.SpecialCells(xlCellTypeConstants)

    On a very sparse matrix on my machine, (3 random blocks of numbers - no more than 10 rows in each block) your original code took 10.22266 seconds. Using special cells cut that to 1.953125E-03 (.00195) seconds. Your results will vary depending on the number of cells to process.

    SpecialCells used to have issues if too many cells were selected (>8192) - I'm not sure if that still applies as I haven't seen that issue lately, and certainly not in this example. If you are using an older (<2007) version of Excel you might come across it.

    If you do, then there's another method where you can get the UsedRange of the worksheet and use the INTERSECT() function to get just those cells in the range you want to process which intersect with the used range:

    Set sumrange = Intersect(ActiveSheet.UsedRange, Range("H:Q"))
    For Each Cell In sumrange

    That executes in 1.367188E-02 (0.0136) seconds.

    However, Intersect will include all blank cells in columns H:Q in the range so you can get rid of them by combining that with SpecialCells

    Set sumrange = Intersect(ActiveSheet.UsedRange, Range("H:Q")).SpecialCells(xlCellTypeConstants)

    That is included just as an example. If the SpecialCells issue occurs on your machine then you'd want to avoid using it, but it executes in 0.00781 seconds. Faster than Intersect on it's own, but slower than SpecialCells as is contains both SpecialCells and Intersect.

    It's getting silly now, but you said you were learning so a couple of examples of how to do one particular task.

    Points to note:

    • There's probably 99 ways (Exaggeration to make a point) to do anything in VBA.
    • Always limit the number of cells you process to the bare minimum (See timing examples).
    • Using inbuilt Excel facilities is always faster. Note the difference between using SpecialCells (Which Excel is programmed to find internally) Vs using Intersect (where Excel has to 'manually' scan the range to find the matching cells).

    A speed-up by a factor of >5000x is a result in any language, human or computer!

    But well done for figuring the basic solution yourself. Always nice to see someone have a go rather than posting a 'problem' and just waiting for a reply.

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!