VBA Insert SUM formula not result of SUM

[tourless]

Hi Folks,

I have invoice data separated with blank lines between invoice numbers and I'm currently using the following code which gives me the result of summing up the line items of a given invoice and it works fine. But what I would like to do is actually insert the sum formula for the rows that make up each invoice and insert that formula in the column to the right of the blank line which marks the end of a given invoice. The code I'm using should make it clearer...

    With ActiveSheet
        lastRow = Cells(Rows.Count, "E").End(xlUp).Row
        firstRow = 6
        TempTotal = 0
        For x = firstRow To lastRow + 1
            If Cells(x, "E") <> "" Then
                TempTotal = TempTotal + Cells(x, "E")
            Else:   Cells(x, "F") = TempTotal
                    Cells(x, "F").Interior.ColorIndex = 6
                    TempTotal = 0
            End If
        Next x
    End With

The result is accurate as far as the math goes but my data range can have a value that extends to four decimal places which I have to truncate to two decimal places which can change the value of the sum and that new value is not reflected in my sum with the code I'm using because it only enters the value of the sum and not the formula for the sum. With that I'm not sure if it would be easier to round to the two decimal places before I run the code above or to try and make the change I'm looking for in the code.

I defer to you experts for advise.

Thanks.

 

[Fluff]

What is in col E? Is it hard values, or formulas?

 

[tourless]

hard values. They are formatted as two digit decimals but can contain values that extend up to four decimal places depending on its value.

 

[Fluff]

Ok, how about

Sub tourless()
   Dim Rng As Range
   For Each Rng In Range("E6", Range("E" & Rows.Count).End(xlUp)).SpecialCells(xlConstants).Areas
      With Rng.Offset(Rng.Count, 1).Resize(1)
         .Formula = "=sum(" & Rng.Address & ")"
         .Interior.ColorIndex = 6
      End With
   Next Rng
End Sub

 

[tourless]

That works great, thank you so much!

 

[Fluff]

You're welcome & thanks for the feedback