Click here to Skip to main content
15,916,318 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more:
I have a data where I have found SUM of some values at some places. The SUM I used in the code was calculated dynamically. I haven't specified the Row or Coulumn, or Range.
So I just want to know now how can I now find the total of all the SUMs I calculated at the end of UsedRange.Rows.Count?

What I have tried:

<pre>For i = 1 To .UsedRange.Rows.Count
        If IsNumeric(.Cells(i, 6).Value) = True And .Cells(i, 6).Value <> "" Then
            If firstnum = "" Then
                firstnum = .Cells(i, 6).Address(False, False)
            End If
            If .Cells(i + 1, 6).Value = "" Then
                lastnum = .Cells(i, 6).Address(False, False)
                i = i + 1
                .Cells(i, 6).Formula = "=SUM(" & firstnum & ":" & lastnum & ")"
                firstnum = ""
            End If

This calculates the SUM of some values entered in D column. The values entered are in irregular pattern, and there's a sum calculated after a block of range ends. So after all such sums, I need a grand sum at the end. The above code calculates the sum finding first number and last number of a block and adds the number in between of them. It will be great if anyone can throw a light on it...
Sinisa Hajnal 15-Dec-17 8:38am    
1st option: make another look and sum content of .Cells(i, 6) (the ones in which you put formula earlier); you may need to keep their identifiers in a collection or an array
2nd option: add variable in the code, sum the results of summary cells and just put the final value into some final cell (no formula that way)
JayyMehta 15-Dec-17 9:29am    
By using the 1st option, if I sum the contents of .Cells(i,6), i'm afraid that the sum of all the blocks would also add up along with the data values. So the total sum would be double I guess...

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900