Results 1 to 3 of 3

Thread: need macro to add up weekend values to weekday

  1. #1

    need macro to add up weekend values to weekday

    Register for a FREE account, and/
    or Log in to avoid these ads!

    Hi Champs,

    I have two columns,
    col A--- Date
    col B --- value for the date
    the date would be all the days, including weekends.
    i need a macro to add up the values (col B) of saturday and sunday to the value of next monday...
    see the attached sample input and output file for the reference.

    Thanks in advance.sample.xlsx

  2. #2
    Super Moderator Bob Phillips's Avatar
    Join Date
    Mar 2011
    Excel Version
    Public Sub TransformData()Dim lastrow As Long
    Dim totWeekend As Long
    Dim i As Long
        Application.ScreenUpdating = False
        With ActiveSheet
            lastrow = .Cells(.Rows.Count, "A").End(xlUp).Row
            For i = 3 To lastrow
                If Weekday(.Cells(i, "A").Value, vbMonday) > 5 Then
                    totWeekend = totWeekend + .Cells(i, "B").Value
                    .Cells(i, "B").ClearContents
                    If Weekday(.Cells(i, "A").Value, vbMonday) = 1 Then
                        .Cells(i, "B").Value = .Cells(i, "B").Value + totWeekend
                        .Cells(i, "B").Interior.ColorIndex = 43
                    End If
                    totWeekend = 0
                End If
            Next i
        End With
        Application.ScreenUpdating = True
    End Sub

  3. #3
    Excellent. your code works fine... Thanks a lot

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts