Excel VBA代码优化：按类别批量替换指定区域0值为对应重量估算公式

Got it, let's refine your VBA code to handle thousands of rows efficiently, fix the existing issues, and make it more maintainable. Here's the breakdown of improvements and the final code:

Key Optimizations Made #

• Fixed the invalid range: Instead of Range("A2:A") (which tries to loop the entire column), we dynamically find the last row with data in column A to only loop through relevant cells. This cuts down on unnecessary iterations drastically.
• Removed redundant code: RNG.Offset(0,0) is exactly the same as RNG itself—we can eliminate this entirely.
• Consolidated conditionals: We check once if the weight is 0, then handle the category logic inside that check, reducing repeated evaluations.
• Used absolute references in formulas: The original Cells(2,5) creates a relative reference that can break if your table moves. We use absolute references like $E$2 to lock the average price cells.
• Added performance boosts: For large datasets, disabling screen updating and manual calculation prevents slowdowns from constant screen refreshes and formula recalculations.

Final Optimized Code #

Sub FillMissingWeights()
    Dim ws As Worksheet
    Dim lastRow As Long
    Dim rng As Range
    Dim avgPriceCell As String
    
    ' Set the worksheet (change to your sheet name if needed)
    Set ws = ThisWorkbook.ActiveSheet
    
    ' Turn off performance-hungry features for large datasets
    Application.ScreenUpdating = False
    Application.Calculation = xlCalculationManual
    
    ' Get the last row with data in column A (weight column)
    lastRow = ws.Cells(ws.Rows.Count, "A").End(xlUp).Row
    
    ' Loop through each relevant cell in column A
    For Each rng In ws.Range("A2:A" & lastRow)
        ' Only process if weight is 0
        If rng.Value = 0 Then
            ' Determine the correct average price cell based on category (column C)
            Select Case rng.Offset(0, 2).Value
                Case "a"
                    avgPriceCell = "$E$2" ' Adjust to your actual average price cell for category a
                Case "b"
                    avgPriceCell = "$E$3" ' Adjust to your actual average price cell for category b
                Case "c"
                    avgPriceCell = "$E$4" ' Adjust to your actual average price cell for category c
                Case Else
                    ' Optional: Handle unexpected categories (leave blank or add a message)
                    avgPriceCell = ""
            End Select
            
            ' Set the formula only if we have a valid category
            If avgPriceCell <> "" Then
                rng.FormulaR1C1 = "=RC[1]/'" & ws.Name & "'!" & avgPriceCell
            End If
        End If
    Next rng
    
    ' Restore Excel default settings
    Application.ScreenUpdating = True
    Application.Calculation = xlCalculationAutomatic
End Sub

Additional Notes #

• Adjust cell references: Double-check that $E$2, $E$3, $E$4 match your actual average price cells. If your averages are in a different column or row, update these values accordingly.
• Worksheet specification: If your data isn't on the active sheet, replace ThisWorkbook.ActiveSheet with ThisWorkbook.Worksheets("YourSheetName") to avoid targeting the wrong sheet.
• Error handling: You can add an error handler (like On Error Resume Next or On Error GoTo ErrorHandler) if you expect unexpected data types in the weight or category columns.

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