VBA：循环遍历行，如果满足条件，则总结某些行 [英] VBA: loop through rows, sum up certain rows if condition is met

问题描述

在学习VBA时，我一直在使用论坛几个星期。但是现在我陷入困境，需要帮助。所以这里是我的问题：

I have been using the forums for a couple of weeks now while learning VBA. But now I am stuck and need help. So here is my problem:

我有一个工作簿，其中有几个工作表。为了简单起见，我们假设我有两个工作表。一个有很多原始数据资料，一个是很好的格式，并将是接收表。

I have a workbook with several worksheets in it. For simplicity let's assume that I have two worksheets. One with a lot of raw data material and one which is nicely formatted and will be the receiving sheet.

原始数据表如下所示：我有8列，我需要检查每行中的两个条件（列B和E），并总结列H中的值。
列B包含不同的名称，列E包含工作状态，例如准备或说话我有约3.000行和一组约20个不同的名称与不同的状态。所以总体来说，我们说100行，名字叫史密斯，状态说话没有特定的顺序。我现在需要总结所有的名字（H）在每个行中，名称史密斯和状态说话的所有数字（列H）。之后，我想要的名字（列B），状态（列E）和总和（列H），写入动态范围内的相同工作簿的单元格，从I3：K3开始。我不得不承认，我在黑暗中总是这样的一个..
我真的希望你们可以帮助我一些代码和/或指向我正确的方向。最重要的是，我希望你也可以解释你的代码是什么，所以我可以理解它，并学习。感谢提前的人！

The raw data table looks like this: I have 8 columns and I need to check for two conditions in each row (columns B and E) and sum up the values in column H. Column B contains different names and column E contains a working status, e.g. ready or talking. I do have about 3.000 rows and a set of about 20 different names with different status. So overall I have, let's say 100 rows with the name "Smith" and the status "talking" in no particular order. I now need to sum up all the numbers (column H) in each of those rows where the name "Smith" and the status "talking" are put in. After that, I would like to have the name (column B), the status (column E) and the sum (column H), to be written into cells of the same workbook in a dynamic range, starting at I3:K3. I have to admit that I am totaly in the dark on this one.. I really hope that you guys can help me with some code and/or point me into the right direction. Most of all I hope that you could also explain what your code does, so I can understand it properly and learn. Thanks in advance guys!

这是我的代码到目前为止：

Here is my code so far:

Private Sub SumNumbers()

Dim tbl As Worksheet
Dim x As Integer
Dim lrow As Long
Dim lastname As String
Dim astatus As String
Dim number As Integer
Dim counter As Integer

Set wb = ThisWorkbook
Set tbl = wb.Sheets("Sheet1")
lrow = tbl.Cells(Rows.Count, "B").End(xlUp).Row
lastname = tbl.Cells("C2:C" & lrow).Text
astatus = tbl.Cells("E2:E" & lrow).Text
number = tbl.Cells("H2:H" & lrow)

For x = 2 To lrow

    If lastname = "Smith" And astatus = "Talking" Then
        counter = counter + number
    End If

Next x

End Sub

推荐答案

你很亲近，但你需要测试每个单元格单独在你的循环。这样的东西：

You're close, but you need to test each cell individually inside your loop. Something like this:

Sub Macro1()
    Dim tbl As Worksheet
    Dim x As Integer
    Dim lrow As Long
    Dim lastname As String
    Dim astatus As String
    Dim number As Integer
    Dim counter As Integer: counter = 3

    Set wb = ThisWorkbook
    Set tbl = wb.Sheets("Sheet1")
    lrow = tbl.Cells(Rows.Count, "B").End(xlUp).Row

    For x = 2 To lrow
        If tbl.Range("C" & x) = "Smith" And tbl.Range("E" & x) = "Talking" Then
            Range("I" & counter).Value = Range("C" & x)
            Range("J" & counter).Value = Range("E" & x)
            Range("K" & counter).Value = Range("H" & x)
            counter = counter + 1
        End If
    Next x
End Sub

---

每个@Tim注释可以使用SumIfs返回总数符合以下多项标准的H栏： p>

---

Per @Tim comment you can use SumIfs to return the total of 'H' column that meet multiple criteria like this:

=SUMIFS(H:H,C:C,"=Smith",E:E,"=Talking")

这篇关于VBA：循环遍历行，如果满足条件，则总结某些行的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！