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

Question

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.

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 :

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

Down to the last row

Are your columns Fixed? i.e B to AF? In other words, is the total always in Col AG? — Siddharth Rout
– Siddharth Rout, Commented Jan 29, 2013 at 7:26

Siddharth Rout · Accepted Answer · 2013-01-29 21:39:51Z

2

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

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 A else See PART B below

PART A

Do you need VBA for this? If your columns are fixed i.e B to AF and your total is always in Col AG then this can be achieved with a simple Excel Formula.

Just enter this formula in Cell AG3 and copy it down

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

Screenshot

enter image description here

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

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

edited Jan 29, 2013 at 21:39

answered Jan 29, 2013 at 7:32

Siddharth Rout

150k18 gold badges212 silver badges258 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Brian Jones Over a year ago

Siddarth - I tried using your VBA code in Part B, but it was giving me a Compile error: Syntax Error on ".Range("AG3:AG" & LRow).Formula = "=SUM(B3:AF3)+COUNTIF(A3:AF3,"TA")*0.5". And I do you more than one variable to SUM and I wasn't sure how to concatenate your Formula.

makciook · Accepted Answer · 2013-01-28 23:11:22Z

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.

Makciook - your VBA worked perfectly!!! I just needed modify my IF statements to account for all text variables.

Collectives™ on Stack Overflow

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

2 Answers 2

1 Comment

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related