VBA-条件求和B列，直到A列中的值更改 [英] VBA - conditional Sum column B until change of value in column A

问题描述

基本说明.我正在一个电子表格中收集所有多个发票数据.可以说，我只有两列分别分隔每个不同的发票:

Basic explanation. I am collecting all my multiple invoice data in one spreadsheet. Lets say I have only two columns that seperate each of the different invoice:

Serial ID.    Value

ABC001        $5.00
ABC001        $5.00
ABC001        $5.00
CBA100        $6.00
CBA100        $4.00
CBA100        $5.00

我想要一个VBA解决方案，该解决方案将遍历各列，直到B列中的值求和为止，然后再将B列中的值求和，然后再将C列中的求和值放在C列中的最后一行.任务看起来像这样:

I want a VBA solution that would loop through columns, sum values in columnB UNTIL there is a change in columnA and THEN would put that sum value on columnC LAST ROW BEFORE THE CHANGE in columnA. The outpoot would look like this:

Serial ID.    Value   Total

ABC001        $5.00   
ABC001        $5.00
ABC001        $5.00   $15.00
CBA100        $6.00
CBA100        $4.00
CBA100        $5.00   $15.00

到目前为止，我发现最接近的是

So far the closest thing I found was the solution by D Mason

但是他的代码存在问题，即columnB地址中的值的总和位于确切的行号中，发生更改(而不是之前)，并且它将像这样运行:

But the problem with his code that the sum of the values in columnB adress is in EXACT row number WHERE THE CHANGE HAPPENS (not BEFORE), and it would run like this:

Serial ID.    Value   Total

ABC001        $5.00   $15.00   
ABC001        $5.00
ABC001        $5.00
CBA100        $6.00   $15.00
CBA100        $4.00
CBA100        $5.00

我敢肯定，我已经明确了我的问题.但以防万一，串行ID始终会保留在同一列中，值也会保留在同一列中.不会执行任何排序.如果整个范围的总和对我也不好，因为可能有重复的发票，并且如果函数使用范围(A:A)查找条件序列号，则会产生错误的结果.在此先感谢您，如果有一些方法可以改善我的问题(如果我得到了正确的答案，并希望更容易找到其他遇到相同问题的人)，请提出建议.

I am sure, that I made my problem clear. But just in case, the Serial Id will always stay in the same column, so will values. No sort will be performed. Sum if of whole range also is not good for me, becouse there may be duplicate invoices, and if function would take range (A:A) to lookup a conditional serial number, it would produce a false result. Thank you in advance, if there is some ways I could improve my question (if I get a correct answer and want it to be easier to find for others with the same problem), write a suggestion.

推荐答案

请尝试一下...

Sub GetTotal()
Dim rng As Range, cell As Range
Dim lr As Long
Dim Total As Double

Application.ScreenUpdating = False
lr = Cells(Rows.Count, 1).End(xlUp).Row
Set rng = Range("A2:A" & lr)
For Each cell In rng
    If cell = cell.Offset(1) Then
        Total = Total + cell.Offset(0, 1)
    Else
        Total = Total + cell.Offset(0, 1)
        cell.Offset(0, 2) = Total
        Total = 0
    End If
Next cell
Columns("C").NumberFormat = "$#,##0.00"
Application.ScreenUpdating = True
End Sub

这篇关于VBA-条件求和B列，直到A列中的值更改的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！