使用命名区域时Range.Copy方法返回公式而非值的问题排查

Hey there! Let's tackle that frustrating #REF! error you're seeing when switching to Range.Copy for better performance. I'll walk through what's causing it and how to fix it, plus clean up a few syntax hiccups in your new code.

Why the #REF! Error Happens #

When you use Range.Copy directly on a range with formulas, Excel copies the relative cell references in those formulas. Since you're pasting into a different column (uClmn increments each loop), the formula references get shifted to cells that don't exist or aren't what you intended—hence the #REF! errors.

Looking at your original code, you only cared about the values from sumCostG and sumCostE (you pulled .Value2), not the formulas themselves. So copying formulas was unnecessary here, and that's the root of the problem.

Step 1: Fix the Syntax Error in Your New Code #

First, your loop has missing End If statements, which would cause a compile error. Let's correct that first (we'll update this further with the fix for #REF!):

For uRow = 1 To 12
    costG = cmp5YrCostGdata.Cells(uRow, uClmn).Value2
    costE = cmp5YrCostEdata.Cells(uRow, uClmn).Value2
    Debug.Print costG, costE
    
    ' Convert Zero Values to Null
    If costE = 0 Then ' Simplified condition (check if it's zero instead of "not zero")
        cmp5YrCostEdata.Cells(uRow, uClmn) = vbNullString
    End If ' Missing in your original code
    
    If costG = 0 Then
        cmp5YrCostGdata.Cells(uRow, uClmn) = vbNullString
    End If ' Missing in your original code
Next uRow

Step 2: Copy Values Instead of Formulas (Fix #REF! and Boost Performance) #

Since you don't need the formulas in the target ranges, use Copy combined with PasteSpecial xlPasteValues to transfer just the cell values. This eliminates the #REF! error entirely and is still way faster than looping through each cell.

Here's the revised, full code with this fix:

' Optional: Add these at the start to boost performance even more
Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual
Application.EnableEvents = False

'Beg in loading wsCost5Yr Worksheet
For frmYr = sYear To wbCurYear
    Debug.Print locID, frmYr
    wsSum.Cells(3, "Q") = frmYr 'Cell Q3 of the Summary Worksheet
    chartYrsCost.Cells(1, uClmn) = frmYr
    
    ' Copy VALUES only from sumCostG to target column
    sumCostG.Copy
    cmp5YrCostGdata.Cells(1, uClmn).PasteSpecial Paste:=xlPasteValues
    
    ' Copy VALUES only from sumCostE to target column
    sumCostE.Copy
    cmp5YrCostEdata.Cells(1, uClmn).PasteSpecial Paste:=xlPasteValues
    
    ' Now clean up zero values
    For uRow = 1 To 12
        costG = cmp5YrCostGdata.Cells(uRow, uClmn).Value2
        costE = cmp5YrCostEdata.Cells(uRow, uClmn).Value2
        Debug.Print costG, costE
        
        If costE = 0 Then
            cmp5YrCostEdata.Cells(uRow, uClmn) = vbNullString
        End If
        
        If costG = 0 Then
            cmp5YrCostGdata.Cells(uRow, uClmn) = vbNullString
        End If
    Next uRow
    
    uClmn = uClmn + 1
Next frmYr

' Restore Excel settings
Application.ScreenUpdating = True
Application.Calculation = xlCalculationAutomatic
Application.EnableEvents = True

Bonus: Even More Performance #

Adding those Application settings at the top turns off screen updates, automatic calculation, and events while your code runs—this will shave off even more time from your load process. Just remember to restore them at the end (you might want to add error handling to ensure settings reset if something goes wrong, but that's an extra polish step).

Why This Works #

• xlPasteValues copies only the calculated values from sumCostG and sumCostE, not the underlying formulas. No references mean no #REF! errors.
• Bulk copying with Copy/PasteSpecial is still way faster than looping through each cell individually, which was your original goal.
• The corrected If statements properly handle zero-to-null conversion without syntax errors.

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