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
    Code:
    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
                Else
                
                    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
  •