vba 如何使用VBA在Excel中对范围内的每一行求和

How to SUM each row in a Range in Excel with VBA

提问by Brian Jones

I have an Attendance spreadsheet where I need to SUM a row and have the totals in the last columns. Each row represents an employee and each column represents a day in the month. The reason I am using VBA is because some date columns will contain text, such as TA for Tardy and need to add 0.5 to the total if TA is present in one or more of the cells in the range.

我有一个出勤电子表格，我需要对一行进行求和，并在最后一列中列出总数。每行代表一名员工，每列代表一个月中的一天。我使用 VBA 的原因是因为某些日期列将包含文本，例如 Tardy 的 TA，并且如果 TA 存在于该范围内的一个或多个单元格中，则需要将 0.5 添加到总数中。

I can only get the first row to populate, but not the rows below it. I am guessing it is because I am not setting my ranges correctly. Here is the code that I have so far:

我只能填充第一行，但不能填充它下面的行。我猜这是因为我没有正确设置我的范围。这是我到目前为止的代码：

Dim wsJAN As Worksheet      'Used to reference the sheet by its TAB name in the WB
Dim LastRow As Long         'Last used row on sheet
Dim tDays As Range          'Total # of days the employee has actually worked
Dim cDays As Range          'Current # of days the employee should have worked
Dim rowEmployee As Range    'Used to define the columns to be used to when adding attendance for each employee row
Dim rCell As Range

LastRow = Cells.Find(What:="*", After:=[A3], SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row

Application.ScreenUpdating = False

Set wsJAN = ThisWorkbook.Sheets("JAN")
Set tDays = wsJAN.Range("AG3")
Set cDays = wsJAN.Range("AH3")
Set rowEmployee = wsJAN.Range("B3:AF3")

tDays = "=SUM(B3:AF3)"
cDays = "=SUM(B3:AF3)"

For Each rCell In rowEmployee
If rCell.Value = "TA" Then

    tDays = tDays + 0.5    ' Add only a half day to the # of days the employee has worked in Column AG if tardy.
    cDays = cDays + 1      ' Add a whole day to current days worked in Column AH if employee is tardy.
End If
Next


Application.ScreenUpdating = True

I have even tried using For i = 1 To LastRow Step 1 around the For Each loop and Do.....Loop Until LastRow with out any success. My rows will always start at row 3 so I need something along the lines of :

我什至尝试使用 For i = 1 To LastRow Step 1 围绕 For Each 循环和 Do.....Loop until LastRow 没有任何成功。我的行总是从第 3 行开始，所以我需要一些类似的东西：

AG3 =SUM(B3:AF3)
AG4 =SUM(B4:AF4)

AG3 =SUM(B3:AF3)
AG4 =SUM(B4:AF4)

Down to the last row

一直到最后一行

采纳答案by makciook

You should be using two loops instead of one - to sum in cols and in rows. For me the easiest way to run through such a regular sheet is to use Offset for the given range. Here's the solution:

您应该使用两个循环而不是一个循环 - 在列和行中求和。对我来说，遍历这样一个常规工作表的最简单方法是对给定范围使用偏移。这是解决方案：

Dim wsJAN As Worksheet      'Used to reference the sheet by its TAB name in the WB
Dim LastRow As Long         'Last used row on sheet
Dim tDays As Double          'Total # of days the employee has actually worked
Dim cDays As Integer          'Current # of days the employee should have worked
Dim rowEmployee As Range    'Used to define the columns to be used to when adding attendance for each employee row
Dim rCell As Range
Dim i As Integer

Application.ScreenUpdating = False

Set wsJAN = ThisWorkbook.Sheets("JAN")
Set rowEmployee = wsJAN.Range("B3:AF3")

i = 0
Do While Range("B3").Offset(i).Value <> Empty        ' for each row
    tDays = 0
    cDays = 0
    For Each rCell In rowEmployee          ' for each column
        If rCell.Offset(i).Value = "TA" Then
            tDays = tDays + 0.5    ' Add only a half day to the # of days the employee has worked in Column AG if tardy.
            cDays = cDays + 1      ' Add a whole day to current days worked in Column AH if employee is tardy.
        End If
    Next
    ' set AG as tDays && AH as cDays
    Range("AG3").Offset(i).Value = tDays
    Range("AH3").Offset(i).Value = cDays
    i = i + 1
Loop

Application.ScreenUpdating = True

Now it counts the TA-s only (your IF statement says so), but you can now easily modify it, to count whatever is needed.

现在它只计算 TA-s（你的 IF 语句是这样说的），但你现在可以轻松地修改它，以计算任何需要的东西。

回答by Siddharth Rout

add 0.5 to the total if TA is present in one or more of the cells in the range.

如果 TA 存在于范围内的一个或多个单元格中，则将 0.5 添加到总数中。

I am confused. Do you want to add .5 for every "TA" or just once if a TA is found. If it is just once then see PART Aelse See PART Bbelow

我很迷惑。您想为每个“TA”添加 0.5 还是在找到 TA 时仅添加一次。如果它只是一次然后看PART A其他见PART B下文

PART A

甲部

Do you need VBA for this? If your columns are fixed i.e Bto AFand your totalis always in Col AGthen this can be achieved with a simple Excel Formula.

你需要VBA吗？如果您的列，即固定B到AF和你total总是在山口AG那么这可以用一个简单的Excel公式来实现。

Just enter this formula in Cell AG3and copy it down

只需在单元格中输入此公式AG3并将其复制下来

=SUM(B3:AF3) + IF(COUNTIF(A3:AF3,"TA")>0,0.5,0)

Screenshot

截屏

If you still want VBA then you can try this as well

如果你仍然想要 VBA 那么你也可以试试这个

Option Explicit

Sub Sample()
    Dim ws As Worksheet
    Dim LRow As Long

    '~~> Change as applicable
    Set ws = ThisWorkbook.Sheets("Sheet1")

    With ws
        '~~> Assuming that the names are in col A
        LRow = .Range("A" & .Rows.Count).End(xlUp).Row

        .Range("AG3:AG" & LRow).Formula = "=SUM(B3:AF3) + IF(COUNTIF(A3:AF3,""TA"")>0,0.5,0)"
    End With
End Sub

PART B

乙部

=SUM(B3:AF3)+COUNTIF(A3:AF3,"TA")*0.5

Code

代码

Option Explicit

Sub Sample()
    Dim ws As Worksheet
    Dim LRow As Long

    '~~> Change as applicable
    Set ws = ThisWorkbook.Sheets("Sheet1")

    With ws
        '~~> Assuming that the names are in col A
        LRow = .Range("A" & .Rows.Count).End(xlUp).Row

        .Range("AG3:AG" & LRow).Formula = "=SUM(B3:AF3)+COUNTIF(A3:AF3,""TA"")*0.5"
    End With
End Sub