如何用VBA统计指定区域内任务的出现次数及对应工时总和

Got it, let's fix this up so you can track all your tasks at once instead of just one. Here's a complete VBA solution that handles both counting task occurrences and summing their total hours, plus outputs everything to your "Weekly Data" sheet as requested:

Sub CalculateTaskStats()
    Dim adminSheet As Worksheet
    Dim weeklyDataSheet As Worksheet
    Dim sourceSheet As Worksheet
    Dim taskListRng As Range
    Dim taskCell As Range
    Dim srchRng As Range
    Dim currentRow As Range
    Dim taskDict As Object ' Late binding for Scripting.Dictionary
    Dim taskName As String
    Dim hoursValue As Double
    Dim outputRow As Long
    
    ' Set worksheet references (adjust if your sheet names differ)
    Set adminSheet = ThisWorkbook.Worksheets("Admin Sheet")
    Set weeklyDataSheet = ThisWorkbook.Worksheets("Weekly Data")
    Set sourceSheet = ActiveSheet ' Or explicitly name the sheet with your dynamic range
    
    ' Grab the full task list from Admin Sheet (assuming tasks are in Column A starting at A2)
    Set taskListRng = adminSheet.Range("A2:A" & adminSheet.Cells(adminSheet.Rows.Count, "A").End(xlUp).Row)
    
    ' Initialize a dictionary to track each task's count and total hours
    Set taskDict = CreateObject("Scripting.Dictionary")
    taskDict.CompareMode = vbTextCompare ' Make matching case-insensitive
    
    ' Define your dynamic search range using the existing rangeString variable
    Set srchRng = sourceSheet.Range(rangeString)
    
    ' Loop through every row in the dynamic range to collect stats
    For Each currentRow In srchRng.Rows
        taskName = Trim(currentRow.Cells(1, "F").Value) ' Task is in Column F
        hoursValue = currentRow.Cells(1, "K").Value ' Hours are in Column K
        
        ' Skip blank task entries to avoid clutter
        If taskName <> "" Then
            If taskDict.Exists(taskName) Then
                ' Update existing task: increment count, add to total hours
                taskDict(taskName)(0) = taskDict(taskName)(0) + 1
                taskDict(taskName)(1) = taskDict(taskName)(1) + hoursValue
            Else
                ' Add new task to the dictionary: start with count 1 and the current hour value
                taskDict.Add taskName, Array(1, hoursValue)
            End If
        End If
    Next currentRow
    
    ' Write results to Weekly Data sheet starting at Row 6
    outputRow = 6
    For Each taskCell In taskListRng
        taskName = Trim(taskCell.Value)
        If taskDict.Exists(taskName) Then
            ' Count goes to Column C, total hours to Column D
            weeklyDataSheet.Cells(outputRow, "C").Value = taskDict(taskName)(0)
            weeklyDataSheet.Cells(outputRow, "D").Value = taskDict(taskName)(1)
        Else
            ' If the task wasn't found, write 0 for both values
            weeklyDataSheet.Cells(outputRow, "C").Value = 0
            weeklyDataSheet.Cells(outputRow, "D").Value = 0
        End If
        outputRow = outputRow + 1
    Next taskCell
    
    MsgBox "Task stats calculated and saved successfully!", vbInformation
End Sub

• Dictionary for Dynamic Tracking: Instead of hardcoding each task like your original code, we use a dictionary to automatically track every task in your dynamic range. This makes the code scalable no matter how many tasks you add.
• Late Binding: Using CreateObject("Scripting.Dictionary") means you don't need to add any special references to your workbook—perfect for sharing with teammates.
• Task List Adjustment: If your task list in "Admin Sheet" isn't in Column A, just change the column letter in the taskListRng line (e.g., use "B2:B..." if tasks are in Column B).
• Case Sensitivity: If you need task names to match exactly (e.g., "AD PAINTING" vs "ad painting"), remove the taskDict.CompareMode = vbTextCompare line.
• Dynamic Range: The code uses your existing rangeString variable, so just make sure that's defined correctly before running the sub (it should be a string like "A1:K200" pointing to your daily-updated data).

内容的提问来源于stack exchange，提问作者J4C3N-14