Excel VBA技术问询：合并H&L行汇总N&Q列，多列按行条件求和

Let's break down your two requirements and adjust the VBA dictionary approach to handle multi-key/multi-value scenarios—since your current single-key single-value setup isn't sufficient for what you need.

Requirement 1: Sum Multiple Columns Based on Row-Specific Conditions #

To sum columns using combined row conditions (multi-key grouping), we can concatenate your condition columns into a single unique key for the dictionary. For example, if you're grouping by columns A and C, we’ll create a key like A_value|B_value to track sums for multiple target columns at once.

Here's a flexible snippet you can adapt to your exact data layout:

Sub SumByRowConditions()
    Dim ws As Worksheet
    Dim countDict As Object
    Dim lastRow As Long, i As Long
    Dim key As String
    Dim sumCol1 As Double, sumCol2 As Double ' Add more variables for additional sum columns
    
    Set ws = ActiveSheet ' Replace with your target worksheet name (e.g., Sheet1)
    Set countDict = CreateObject("Scripting.Dictionary")
    lastRow = ws.Cells(ws.Rows.Count, "A").End(xlUp).Row ' Adjust column to find your data's last row
    
    ' Loop through data rows (skip header by starting at row 2 if needed)
    For i = 2 To lastRow
        ' Create unique key from your condition columns (example: columns A and C)
        key = ws.Cells(i, "A").Value & "|" & ws.Cells(i, "C").Value
        
        ' Pull existing sums from dictionary (default to 0 if key is new)
        sumCol1 = IIf(countDict.Exists(key), countDict(key)(0), 0)
        sumCol2 = IIf(countDict.Exists(key), countDict(key)(1), 0)
        
        ' Add current row's values to the running totals
        sumCol1 = sumCol1 + ws.Cells(i, "D").Value ' Target column 1 to sum
        sumCol2 = sumCol2 + ws.Cells(i, "E").Value ' Target column 2 to sum
        
        ' Store updated sums in the dictionary as an array
        countDict(key) = Array(sumCol1, sumCol2)
    Next i
    
    ' Output results (example: write to columns G-J starting at row 2)
    Dim outputRow As Long
    outputRow = 2
    For Each key In countDict.Keys
        ws.Cells(outputRow, "G").Value = Split(key, "|")(0) ' First condition column value
        ws.Cells(outputRow, "H").Value = Split(key, "|")(1) ' Second condition column value
        ws.Cells(outputRow, "I").Value = countDict(key)(0) ' Sum of first target column
        ws.Cells(outputRow, "J").Value = countDict(key)(1) ' Sum of second target column
        outputRow = outputRow + 1
    Next key
End Sub

How this works: #

• The concatenated string key lets us group rows by multiple conditions
• The dictionary stores an array of sums, so we can track totals for several columns simultaneously
• Adjust the condition columns (A, C) and sum columns (D, E) to match your actual data

Requirement 2: Merge Rows H & L, Sum Columns N & Q, Output to Sheet "X" #

For merging rows H (row 8) and L (row 12) and summing their N (column 14) and Q (column 17) values, we can directly reference these rows, calculate totals, and write results to the "X" worksheet. If "X" doesn’t exist, the code will create it automatically.

Here's the code for this task:

Sub MergeRowsAndSumToX()
    Dim sourceWs As Worksheet
    Dim targetWs As Worksheet
    Dim sumN As Double, sumQ As Double
    
    Set sourceWs = ActiveSheet ' Replace with your source worksheet name
    On Error Resume Next
    Set targetWs = ThisWorkbook.Worksheets("X")
    On Error GoTo 0
    
    ' Create sheet "X" if it doesn't exist
    If targetWs Is Nothing Then
        Set targetWs = ThisWorkbook.Worksheets.Add(After:=ThisWorkbook.Worksheets(ThisWorkbook.Worksheets.Count))
        targetWs.Name = "X"
    End If
    
    ' Calculate sums for columns N and Q from rows H (8) and L (12)
    sumN = sourceWs.Cells(8, "N").Value + sourceWs.Cells(12, "N").Value
    sumQ = sourceWs.Cells(8, "Q").Value + sourceWs.Cells(12, "Q").Value
    
    ' Output results to sheet "X" (adjust positions as needed)
    targetWs.Cells(1, "A").Value = "Total Column N (Rows H & L)"
    targetWs.Cells(1, "B").Value = sumN
    targetWs.Cells(2, "A").Value = "Total Column Q (Rows H & L)"
    targetWs.Cells(2, "B").Value = sumQ
    
    ' Optional: Merge text data from the two rows (example: column A values)
    ' targetWs.Cells(3, "A").Value = sourceWs.Cells(8, "A").Value & " / " & sourceWs.Cells(12, "A").Value
End Sub

Notes: #

• Uncomment the optional line if you need to combine non-numeric data from rows H and L
• Adjust the output cell positions (A1, B1, etc.) to match where you want results in sheet "X"

Run Both Tasks in One Macro (Optional) #

If you want to execute both requirements with a single click, call both subs from a main routine:

Sub RunAllTasks()
    Call SumByRowConditions
    Call MergeRowsAndSumToX
End Sub

内容的提问来源于stack exchange，提问作者MSY