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

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.