Error in VBA code while Auto Sum based on IF

Jyotirmaya

Board Regular
Joined
Dec 2, 2015
Messages
168
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.

ques1JPG.JPG


I am using the code

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.

quesJPG.JPG


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 ??
 

Some videos you may like

Excel Facts

Best way to learn Power Query?
Read M is for (Data) Monkey book by Ken Puls and Miguel Escobar. It is the complete guide to Power Query.

Takae

Well-known Member
Joined
Jul 1, 2015
Messages
718
Maybe...
Code:
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

Board Regular
Joined
Dec 2, 2015
Messages
168
Maybe...
Code:
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

Well-known Member
Joined
Jul 1, 2015
Messages
718
Sorry, forgot the last line.

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

Board Regular
Joined
Dec 2, 2015
Messages
168

ADVERTISEMENT

Sorry, forgot the last line.

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

Well-known Member
Joined
Jul 1, 2015
Messages
718
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

Board Regular
Joined
Dec 2, 2015
Messages
168
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

Grand%2Btotal%2Berror.JPG
 

Watch MrExcel Video

Forum statistics

Threads
1,122,575
Messages
5,596,950
Members
414,114
Latest member
Lost_User21

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top