Excel Forum

Code: Select all

Sub CalculateSumsCustom()
    Dim wsWL As Worksheet
    Dim wsSUM As Worksheet
    Dim lastRow As Long
    Dim dateCell As Range
    Dim dateVal As Integer
    Dim colHeaders As Range
    Dim sumHeaders As Range
    Dim headerMatch As Range
    Dim i As Long, j As Long
    Dim cellValue As Double
    
    Application.ScreenUpdating = False
    
    Set wsWL = ThisWorkbook.Sheets("W-L")
    Set wsSUM = ThisWorkbook.Sheets("SUM")
    
    
    lastRow = wsWL.Cells(wsWL.Rows.Count, "I").End(xlUp).Row
    
    
    Set colHeaders = wsWL.Range("J1:CP1")
    Set sumHeaders = wsSUM.Range("C1:CI1")
    
    
    wsSUM.Range("C3:CI3").Value = 0
    wsSUM.Range("C6:CI6").Value = 0
    wsSUM.Range("C9:CI9").Value = 0
    wsSUM.Range("C12:CI12").Value = 0

    For i = 2 To lastRow
        
        Set dateCell = wsWL.Cells(i, "I")
        If IsDate(dateCell.Value) Then
            dateVal = Day(dateCell.Value)
            
            
            If dateVal >= Sheet6.[c20].Value And dateVal <= Sheet6.[d20].Value Then
                Set sumRange = wsSUM.Range("C3:CI3")
            ElseIf dateVal >= Sheet6.[c21].Value And dateVal <= Sheet6.[d21].Value Then
                Set sumRange = wsSUM.Range("C6:CI6")
            ElseIf dateVal >= Sheet6.[c22].Value And dateVal <= Sheet6.[d22].Value Then
                Set sumRange = wsSUM.Range("C9:CI9")
            ElseIf dateVal >= Sheet6.[c23].Value And dateVal <= Sheet6.[d23].Value Then
                Set sumRange = wsSUM.Range("C12:CI12")
            Else
            GoTo NextRow
            End If
            
        
            For j = 1 To 87
                If IsNumeric(wsWL.Cells(i, j + 9).Value) Then
                cellValue = wsWL.Cells(i, j + 9).Value
                Else
                    cellValue = 0
                End If
            
            sumRange.Cells(1, j).Value = sumRange.Cells(1, j).Value + cellValue
            Next j
        End If
        
NextRow:
    Next i
    
    MsgBox "Calculation complete!"
End Sub

Code: Select all

Sub CalculateSums()
    Dim wsWL As Worksheet
    Dim wsSUM As Worksheet
    Dim lastRow As Long
    Dim dateCell As Range
    Dim dateVal As Integer
    Dim colHeaders As Range
    Dim sumHeaders As Range
    Dim headerMatch As Range
    Dim i As Long, j As Long
    Dim cellValue As Double
    
    Application.ScreenUpdating = False
    
    ' Set worksheets
    Set wsWL = ThisWorkbook.Sheets("W-L")
    Set wsSUM = ThisWorkbook.Sheets("SUM")
    
    ' Find the last row with data in W/L sheet
    lastRow = wsWL.Cells(wsWL.Rows.Count, "I").End(xlUp).Row
    
    ' Set column headers ranges
    Set colHeaders = wsWL.Range("J1:CP1")
    Set sumHeaders = wsSUM.Range("C1:CI1")
    
    ' Initialize the result ranges in SUM sheet to 0
    wsSUM.Range("C3:CI3").Value = 0
    wsSUM.Range("C6:CI6").Value = 0
    wsSUM.Range("C9:CI9").Value = 0
    wsSUM.Range("C12:CI12").Value = 0
    
    ' Loop through each row in W/L sheet
    For i = 2 To lastRow
        ' Get date value
        Set dateCell = wsWL.Cells(i, "I")
        If IsDate(dateCell.Value) Then
            dateVal = Day(dateCell.Value)
            
            ' Check which date range the date falls into
            If dateVal >= 1 And dateVal <= 7 Then
                Set sumRange = wsSUM.Range("C3:CI3")
            ElseIf dateVal >= 8 And dateVal <= 14 Then
                Set sumRange = wsSUM.Range("C6:CI6")
            ElseIf dateVal >= 15 And dateVal <= 21 Then
                Set sumRange = wsSUM.Range("C9:CI9")
            ElseIf dateVal >= 22 And dateVal <= 31 Then
                Set sumRange = wsSUM.Range("C12:CI12")
            Else
                GoTo NextRow ' Skip this row if date is not within 1-31
            End If
            
            ' Loop through each column header in W/L sheet
            For j = 1 To 87
                If IsNumeric(wsWL.Cells(i, j + 9).Value) Then
                cellValue = wsWL.Cells(i, j + 9).Value
                Else
                    cellValue = 0
                End If
            
            sumRange.Cells(1, j).Value = sumRange.Cells(1, j).Value + cellValue
            Next j
        End If
        
NextRow:
    Next i
    
    MsgBox "Calculation complete!"
End Sub