Excel VBA运行时错误'13'：无法复制Breakdown表并汇总持仓

Common Causes & Solutions #

1. Non-Numeric Values in Portfolio Value Column #

The most likely issue is non-numeric entries (text, blanks, error values like #N/A or #VALUE!) in your Portfolio Value column. VBA can’t sum these invalid entries, triggering a type mismatch.

• Clean the data: Scan the Breakdown sheet’s Portfolio Value column and fix non-numeric cells (convert text to numbers, remove errors).
• Add error handling: Use IsNumeric() and IsError() checks in code to skip or default invalid values.

2. Incorrect Range/Column References #

If your code targets the wrong column (e.g., a text column instead of Portfolio Value), summing will fail.

• Verify column positions: Double-check that you’re referencing the correct column for Portfolio Value (e.g., use Cells(i, 4) if it’s column D).
• Use named ranges: Define a named range for Portfolio Value in Excel to avoid hardcoding column numbers.

3. Misdeclared Variables #

Variables assigned to the wrong data type (e.g., a string variable holding a numeric value) cause mismatches.

• Declare explicitly: Use Dim with correct types (e.g., Dim portfolioVal As Double instead of generic Variant).
• Enable Option Explicit: Add this at the top of your module to catch undeclared variables, a common source of type errors.

Corrected VBA Code Example #

This robust version handles invalid data and properly summarizes by Country and GICS Sector:

Option Explicit

Sub SummarizePortfolio()
    Dim breakdownWs As Worksheet, macroWs As Worksheet
    Dim lastRow As Long, i As Long, summaryRow As Long
    Dim country As String, sector As String
    Dim portfolioVal As Double
    Dim summaryDict As Object
    
    ' Set worksheet references
    Set breakdownWs = ThisWorkbook.Worksheets("Breakdown")
    Set macroWs = ThisWorkbook.Worksheets("Macro")
    Set summaryDict = CreateObject("Scripting.Dictionary")
    
    ' Clear existing summary data (preserve headers if row 1 is header)
    macroWs.UsedRange.Offset(1).ClearContents
    
    ' Get last row in Breakdown sheet
    lastRow = breakdownWs.Cells(breakdownWs.Rows.Count, "A").End(xlUp).Row
    
    ' Loop through data (skip header row)
    For i = 2 To lastRow
        country = Trim(breakdownWs.Cells(i, "B").Value) ' Adjust column to match your Country column
        sector = Trim(breakdownWs.Cells(i, "C").Value) ' Adjust column to match your GICS Sector column
        
        ' Skip rows with empty Country/Sector
        If country <> "" And sector <> "" Then
            ' Handle non-numeric Portfolio Value
            If IsNumeric(breakdownWs.Cells(i, "D").Value) And Not IsError(breakdownWs.Cells(i, "D").Value) Then
                portfolioVal = CDbl(breakdownWs.Cells(i, "D").Value) ' Adjust column to match Portfolio Value
            Else
                portfolioVal = 0 ' Treat invalid values as 0
            End If
            
            ' Create unique key for Country-Sector pair
            Dim key As String
            key = country & "|" & sector
            
            ' Update summary dictionary
            If summaryDict.Exists(key) Then
                summaryDict(key) = summaryDict(key) + portfolioVal
            Else
                summaryDict.Add key, portfolioVal
            End If
        End If
    Next i
    
    ' Write summary to Macro sheet
    summaryRow = 2
    For Each key In summaryDict.Keys
        Dim parts() As String
        parts = Split(key, "|")
        
        macroWs.Cells(summaryRow, "A").Value = parts(0) ' Country
        macroWs.Cells(summaryRow, "B").Value = parts(1) ' GICS Sector
        macroWs.Cells(summaryRow, "C").Value = summaryDict(key) ' Total Portfolio Value
        summaryRow = summaryRow + 1
    Next key
    
    ' Optional: Format currency column
    macroWs.Range("C:C").NumberFormat = "$#,##0.00"
    
    MsgBox "Summarization done!", vbInformation
End Sub

Notes #

• Adjust column letters (B, C, D) to match your actual Breakdown sheet structure.
• The code uses late binding for Scripting.Dictionary (no need to enable extra libraries).
• Empty Country/Sector entries are skipped, and invalid Portfolio Value cells are treated as 0 to avoid errors.

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