Error in VBA code while Auto Sum based on IF

[Jyotirmaya]

I want a <acronym title="visual basic for applications" style="border-width: 0px 0px 1px; border-top-style: initial; border-right-style: initial; border-bottom-style: dotted; border-left-style: initial; border-top-color: initial; border-right-color: initial; border-bottom-color: rgb(0, 0, 0); border-left-color: initial; border-image: initial; cursor: help; color: rgb(51, 51, 51); background-color: rgb(250, 250, 250);">VBA</acronym> Macro for Automatically Total for column AH depending on AG column, for example if there will be any Text in

Column B then the Total of AG column of that particular item should be total on column AH.

If any new item will be added in Column B then the depending values of that item of column AG should show the sum in the last

row of column AG. I have some formula in Column AG, the values of AG is automatically deriving a formula.

I am using the code

Sub Grand_Total()
    Dim x As Long
    Dim c As Long
    Dim t As Long
    Dim sumrange As Long
    Application.EnableEvents = False
    Application.ScreenUpdating = False
    Columns(34).ClearContents
    x = Cells(Rows.Count, 2).End(xlUp).Row
    If Cells(2, 2) <> "" Then
        t = 2
    Else
        t = Cells(2, 2).End(xlDown).Row
    End If
    For c = t To x
        If Cells(c, 2) <> "" Then
            sumrange = Application.WorksheetFunction.Sum(Range(Cells(c, 2), Cells(c, 2).End(xlDown).Offset(-1)).Offset(, 31))
        End If
        If Cells(c, 2).End(xlDown).Row <> 1048576 Then
            Cells(c, 2).End(xlDown).Offset(-1, 32) = sumrange
        Else
            Cells(c, 2).Offset(, 32) = sumrange
        End If
    Next c
    Application.EnableEvents = True
    Application.ScreenUpdating = True
End Sub

But with the code there are some errors.

With the code it should show AH7 as value 4, similarly AH8 AS 100. But with the code it only sums some rows and leaves some rows. I dont know why, so what should be the change in code ??

 

[Takae]

Maybe...

Sub Grand_Total()
    Dim i As Long, j As Long, LR As Long, cnt As Long
    
    'Application.ScreenUpdating = False
    Columns(34).ClearContents
    LR = cells(Rows.Count, 33).End(xlUp).Row
    For i = 2 To LR
        If cells(i - 1, 2).Value = cells(i, 2).Value Or cells(i, 2).Value = "" Then
            cnt = cnt + cells(i, 33).Value
        Else
            cells(i - 1, 34).Value = cnt
            cnt = cells(i, 33).Value
        End If
    Next
End Sub

 

[Jyotirmaya]

Maybe...

Sub Grand_Total()
    Dim i As Long, j As Long, LR As Long, cnt As Long
    
    'Application.ScreenUpdating = False
    Columns(34).ClearContents
    LR = cells(Rows.Count, 33).End(xlUp).Row
    For i = 2 To LR
        If cells(i - 1, 2).Value = cells(i, 2).Value Or cells(i, 2).Value = "" Then
            cnt = cnt + cells(i, 33).Value
        Else
            cells(i - 1, 34).Value = cnt
            cnt = cells(i, 33).Value
        End If
    Next
End Sub

Hello, Thanks this code works great, but it doesn't include last rows value. It shows total excluding the last row.

 

[Takae]

Sorry, forgot the last line.

Sub Grand_Total()
    Dim i As Long, j As Long, LR As Long, cnt As Long
    
    'Application.ScreenUpdating = False
    Columns(34).ClearContents
    LR = cells(Rows.Count, 33).End(xlUp).Row
    For i = 2 To LR
        If cells(i - 1, 2).Value = cells(i, 2).Value Or cells(i, 2).Value = "" Then
            cnt = cnt + cells(i, 33).Value
        Else
            cells(i - 1, 34).Value = cnt
            cnt = cells(i, 33).Value
        End If
    Next
     cells(i - 1, 34).Value = cnt
End Sub

 

[Jyotirmaya]

Sorry, forgot the last line.

Sub Grand_Total()
    Dim i As Long, j As Long, LR As Long, cnt As Long
    
    'Application.ScreenUpdating = False
    Columns(34).ClearContents
    LR = cells(Rows.Count, 33).End(xlUp).Row
    For i = 2 To LR
        If cells(i - 1, 2).Value = cells(i, 2).Value Or cells(i, 2).Value = "" Then
            cnt = cnt + cells(i, 33).Value
        Else
            cells(i - 1, 34).Value = cnt
            cnt = cells(i, 33).Value
        End If
    Next
     cells(i - 1, 34).Value = cnt
End Sub

It still didn't sum the last row total, it shows only when I add any new text in B column then it shows else it doesn't shows

 

[Takae]

I have tested with your first sample but the 50 of AH17(in your sample) does not show?
It runs perfectly in my PC.
And do you want to run it when you change columnB only? (It should be event macro.)

 

[Jyotirmaya]

I have tested with your first sample but the 50 of AH17(in your sample) does not show?
It runs perfectly in my PC.
And do you want to run it when you change columnB only? (It should be event macro.)

No No I dont want to run when it when Column B changes, I want to run after entering the data. but it doesn't show value of last row, else its perfect