跨2个工作表使用SUMIF出现运行时错误13，求助解决

Hey there, let's tackle this Run-time error 13 (Type Mismatch) issue you're facing with your VBA code. I know spending hours stuck on a bug can be super frustrating, so let's break this down step by step to get it fixed.

First, let's understand why Error 13 happens here #

Run-time error 13 (Type Mismatch) almost always means your code is trying to compare or operate on two values of incompatible data types. For your scenario, common triggers include:

• Mixed data types in columns: Sheet1's A column has text values, but Sheet2's G column has numeric values (or vice versa) and you're comparing them directly without conversion.
• Error values in cells: Cells in Sheet2's G or N column have errors like #N/A, #VALUE!, or #DIV/0! that break calculations.
• Unchecked empty cells: Your code is trying to process blank cells, which can cause type conflicts.
• Incorrect variable typing: If you explicitly declared variables (e.g., Dim myVal As Integer) but the cell content is a text string, this will throw a mismatch.

Step-by-step troubleshooting & fixes #

Let's start with a robust, error-handled version of your code that addresses all these edge cases. I'll add comments to explain each key change:

Sub CalculateValueSummary()
    Dim wsSource As Worksheet, wsTarget As Worksheet
    Dim lastRowSource As Long, lastRowTarget As Long
    Dim i As Long, j As Long
    Dim targetKey As Variant
    Dim occurrenceCount As Long, totalSum As Double
    
    ' Set worksheet references (adjust names if yours are different)
    Set wsSource = ThisWorkbook.Worksheets("Sheet2")
    Set wsTarget = ThisWorkbook.Worksheets("Sheet1")
    
    ' Get the last row with data in each sheet
    lastRowTarget = wsTarget.Cells(wsTarget.Rows.Count, "A").End(xlUp).Row
    lastRowSource = wsSource.Cells(wsSource.Rows.Count, "G").End(xlUp).Row
    
    ' Loop through each unique value in Sheet1's Column A
    For i = 2 To lastRowTarget ' Skip row 1 if it's a header
        targetKey = wsTarget.Cells(i, "A").Value
        
        ' Skip empty cells to avoid type conflicts
        If IsEmpty(targetKey) Then GoTo NextTargetRow
        
        ' Reset counters for each new value
        occurrenceCount = 0
        totalSum = 0
        
        ' Loop through Sheet2's data to match values
        For j = 2 To lastRowSource ' Skip row 1 if it's a header
            ' Skip cells with error values (they break comparisons)
            If IsError(wsSource.Cells(j, "G").Value) Then GoTo NextSourceRow
            
            ' Convert both values to strings to avoid type mismatch
            ' Use vbTextCompare to ignore case differences (optional)
            If StrComp(CStr(wsSource.Cells(j, "G").Value), CStr(targetKey), vbTextCompare) = 0 Then
                occurrenceCount = occurrenceCount + 1
                
                ' Only add N column values if they're numeric
                If IsNumeric(wsSource.Cells(j, "N").Value) Then
                    totalSum = totalSum + wsSource.Cells(j, "N").Value
                End If
            End If
            
NextSourceRow:
        Next j
        
        ' Write results to Sheet1 (adjust columns B/C to your needs)
        wsTarget.Cells(i, "B").Value = occurrenceCount
        wsTarget.Cells(i, "C").Value = totalSum
        
NextTargetRow:
    Next i
    
    MsgBox "Summary calculation complete!", vbInformation
End Sub

Key improvements in this code: #

1. Empty cell handling: Uses IsEmpty to skip blank cells in Sheet1's A column.
2. Error value protection: Uses IsError to ignore broken cells in Sheet2's G column.
3. Type-safe comparisons: Converts both values to strings with CStr before comparing, eliminating numeric/text mismatches.
4. Numeric check for sums: Uses IsNumeric to ensure only valid numbers from Sheet2's N column are added to the total.

Bonus: Faster performance with a Dictionary #

If you're working with large datasets, the double loop above can be slow. Here's an optimized version using a Scripting.Dictionary to avoid redundant checks:

Sub CalculateSummaryWithDictionary()
    Dim wsSource As Worksheet, wsTarget As Worksheet
    Dim lastRowSource As Long, lastRowTarget As Long
    Dim i As Long
    Dim valueDict As Object
    Dim sourceKey As Variant, targetKey As Variant
    Dim nValue As Double
    
    ' Initialize dictionary to store counts and sums
    Set valueDict = CreateObject("Scripting.Dictionary")
    Set wsSource = ThisWorkbook.Worksheets("Sheet2")
    Set wsTarget = ThisWorkbook.Worksheets("Sheet1")
    
    lastRowSource = wsSource.Cells(wsSource.Rows.Count, "G").End(xlUp).Row
    
    ' Populate dictionary with Sheet2 data
    For i = 2 To lastRowSource
        sourceKey = wsSource.Cells(i, "G").Value
        
        ' Skip errors and empty cells
        If Not IsError(sourceKey) And Not IsEmpty(sourceKey) Then
            sourceKey = CStr(sourceKey) ' Standardize to string key
            
            If valueDict.Exists(sourceKey) Then
                ' Update existing entry
                valueDict(sourceKey)(0) = valueDict(sourceKey)(0) + 1
                nValue = IIf(IsNumeric(wsSource.Cells(i, "N").Value), wsSource.Cells(i, "N").Value, 0)
                valueDict(sourceKey)(1) = valueDict(sourceKey)(1) + nValue
            Else
                ' Create new entry with [count, sum]
                nValue = IIf(IsNumeric(wsSource.Cells(i, "N").Value), wsSource.Cells(i, "N").Value, 0)
                valueDict.Add sourceKey, Array(1, nValue)
            End If
        End If
    Next i
    
    ' Write results to Sheet1
    lastRowTarget = wsTarget.Cells(wsTarget.Rows.Count, "A").End(xlUp).Row
    For i = 2 To lastRowTarget
        targetKey = CStr(wsTarget.Cells(i, "A").Value)
        
        If valueDict.Exists(targetKey) Then
            wsTarget.Cells(i, "B").Value = valueDict(targetKey)(0)
            wsTarget.Cells(i, "C").Value = valueDict(targetKey)(1)
        Else
            ' Set 0 if the value doesn't exist in Sheet2
            wsTarget.Cells(i, "B").Value = 0
            wsTarget.Cells(i, "C").Value = 0
        End If
    Next i
    
    MsgBox "Fast summary calculation complete!", vbInformation
    Set valueDict = Nothing
End Sub

This version runs in linear time instead of quadratic time, making it way faster for large datasets.

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