首页 文章

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

提问于
浏览 次
1

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

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

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

我甚至尝试在For Each循环中使用For i = 1 To LastRow Step 1并且Do .....循环直到LastRow没有任何成功 . 我的行总是从第3行开始，所以我需要的是：

AG3 = SUM（B3：AF3）
AG4 = SUM（B4：AF4）

直到最后一排

excel excel-vba vba

2 回答

  • 2

    你应该使用两个循环而不是一个 - 以cols和in行相加 . 对我来说，运行这种常规工作表的最简单方法是在给定范围内使用Offset . 这是解决方案：

    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

    现在它仅计算TA-s（您的IF语句如此表示），但您现在可以轻松地修改它，计算所需的数量 .

    回复于
  • 0

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

    我很迷惑 . 你想为每个_1585499添加.5，或者如果找到TA则只添加一次 . 如果只是一次，请参阅 PART A 否则请参阅下面的 PART B

    PART A

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

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

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

    Screenshot

    enter image description here

    如果您仍然想要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

    码

    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
    回复于

相关问题