VBA Insert SUM formula not result of SUM

tourless

Board Regular
Joined
Feb 8, 2007
Messages
68
Office Version
365
Platform
Windows
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...

VBA Code:
    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.
 

Some videos you may like

Excel Facts

Get help while writing formula
Click the italics "fx" icon to the left of the formula bar to open the Functions Arguments dialog. Help is displayed for each argument.

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
40,956
Office Version
365
Platform
Windows
What is in col E? Is it hard values, or formulas?
 

tourless

Board Regular
Joined
Feb 8, 2007
Messages
68
Office Version
365
Platform
Windows
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

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
40,956
Office Version
365
Platform
Windows
Ok, how about
VBA Code:
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

Board Regular
Joined
Feb 8, 2007
Messages
68
Office Version
365
Platform
Windows
That works great, thank you so much!
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
40,956
Office Version
365
Platform
Windows
You're welcome & thanks for the feedback
 

Watch MrExcel Video

Forum statistics

Threads
1,099,571
Messages
5,469,467
Members
406,654
Latest member
MR_EXCEL_16

This Week's Hot Topics

Top