VBA/Macro help

Discussion in 'OT Technology' started by phproxy, Jan 14, 2009.

  1. phproxy

    phproxy OT Supporter

    Joined:
    Sep 17, 2005
    Messages:
    9,983
    Likes Received:
    2
    I have a simple excel sheet with this information in it:

    A B C
    3 10
    4 10
    5 10
    6 15
    7 10
    1 12
    2 10
    3 12
    4 12
    5 12

    Where the numbers in column A refer to days of the week. 1 = Sunday, 7 = Saturday.

    The numbers in column B refer to hours worked per day.

    I want to total up the total hours per week (so from 2 to 1 / mon-sun) and paste the amount into column C

    I've tried with this code but it only works for the first block of seven :wtc:

    Code:
    Sub x()
    
        Dim WeeklyTotal As Long
        Dim rngCell As Range
        Dim rngOutput As Range
        
         Set rngOutput = Range("C4")
        If Len(rngOutput.Value) > 0 Then
            ' get cell below last used cell in column F
            Set rngOutput = Cells(Rows.Count, 6).End(xlUp).Offset(1, 0)
        End If
        
        For Each rngCell In Range("A4:A40")
        
            If rngCell.Value = 1 Then
                rngOutput.Value = WeeklyTotal + rngCell.Offset(0, 1).Value
                WeeklyTotal = 0
                Set rngOutput = rngOutput.Offset(1, 0) ' move down a row
                
            End If
        
            If rngCell.Value <> 1 Then
            WeeklyTotal = WeeklyTotal + rngCell.Offset(0, 1).Value
            
            End If
        Next
    
       
        
    End Sub
    
     

Share This Page