Add the sum of the total amount from the sheet

Question

Thanks for opening my thread. I need help from you. So in this sheet the total amount should be sum of fuel + surge + delivery_charge should be added in Total amount column.

Ex:- 1st order = 456777 this should add 109.49+303.41+25966.51 = 26379.41 2nd order = 23213213 should add 10+11318+65 = 11393

Dim i As Long, lastrow As Long, rng As Long
lastrow = Range("A" & Rows.Count).End(xlUp).Row

For i = 2 To lastrow
    If Cells(i, "A") <> "" Then
        rng = Cells(i, "A").End(xlDown).Row - 1
        Cells(i, "B").Value = WorksheetFunction.Sum(Range("C" & i & ":C" & rng), Range("E" & i & ":E" & rng), Range("G" & i & ":G" & rng))
    End If
Next i

I'm getting output from this logic :

But the issue is for 2nd order id. It should take only that row. But here its taking 5th and 6th row for addition. 2nd order id= 23213213 total_amt should be 11393.

So anyone could you please help me to find out an issue.

Thanks and Regards, Ranger

Answer 1

The issue is in how you find rng.

rng = Cells(i, "A").End(xlDown).Row - 1

You're using xlDown to find the start of the next block of data. This works as you'd expect, except where the data changes every cell. If you click on cell A5 and press CTRL-Shift-Down, you'll see that it selects three cells as it jumps to the end of that block of data (A7). This is normal Excel behaviour.

You could re-write your code to loop through all the data until it finds the right scenario, but in this case I believe the single cell issue can just be trapped with the right If statement.

If Cells(i + 1, "A") <> "" Then rng = i

Try this:

Dim i As Long, lastrow As Long, rng As Long
lastrow = Range("A" & Rows.Count).End(xlUp).Row

For i = 2 To lastrow
    If Cells(i, "A") <> "" Then
        rng = Cells(i, "A").End(xlDown).Row - 1
        If Cells(i + 1, "A") <> "" Then rng = i
        Cells(i, "B").Value = WorksheetFunction.Sum(Range("C" & i & ":C" & rng), Range("E" & i & ":E" & rng), Range("G" & i & ":G" & rng))
    End If
Next i