多参数毛利率计算VBA宏报错求助：解决对象定义错误

问题说明 #

我需要编写基于3列多参数计算毛利率的VBA宏，但代码在If ws.Cells(i, "L").Value > 0 Then处触发「应用程序定义或对象定义错误」。以下是初始代码和更新后的代码，需修复该错误以完成后续扩展。

初始代码 #

Sub MARGIN_TWO()

    Dim i As Long, ws As Worksheet
    Set ws = ActiveWorkbook.Worksheets("Summary")
    
    Dim lastRow As Integer 'object creation
    lastRow = Range("I" & Rows.Count).End(xlUp).Row
    
    For Each cell In Range("M7:M" & lastRow)
        If ws.Cells(i, "L").Value > 0 Then
            ws.Cells(i, "L").Value
        
        ElseIf ws.Cells(i, "R").Value > 0 Then  

        Select Case ws.Cells(i, "F").Value = "Removed"
        
        Case ws.Cells(i, "X").Value = "L"
            Active.Formula = "formula"

        Case ws.Cells(i, "X").Value = "M"
            Active.Formula = "formula"

        Case ws.Cells(i, "X").Value = "R"
            Active.Formula = "formula"
        
        Case ws.Cells(i, "X").Value = "B"
            Active.Formula = "N/A"
        
        Case ws.Cells(i, "R").Value = 0 Or ws.Cells(i, "R").Value = "#N/A"
            Active.Formula = "N/A"
            
        End Select
        End If
    Next
End Sub

更新后代码 #

Dim i As Integer, ws As Worksheet
Set ws = ActiveWorkbook.Worksheets("Summary")

Dim lastRow As Long 'object creation
lastRow = Range("I" & Rows.Count).End(xlUp).Row
 i = i + 1 'Start i off as 1
For Each cell In Range("M7:M" & lastRow)
    
    If ws.Cells(i, "L").Value > 0 Then
        ActiveCell.FormulaR1C1 = "=RC[-1]"
        i = i + 1
        
    ElseIf ws.Cells(i, "R").Value > 0 Then  'removed-rebated by PGC (i, "R").Value > 0

    Select Case ws.Cells(i, "F").Value = "Removed"
    
    Case ws.Cells(i, "X").Value = "L"
        ActiveCell.FormulaR1C1 = "formula"
        i = i + 1
    Case ws.Cells(i, "X").Value = "M"
        ActiveCell.FormulaR1C1 = "formula"
        i = i + 1
    Case ws.Cells(i, "X").Value = "R"
        ActiveCell.FormulaR1C1 = "formula"
         i = i + 1
    Case ws.Cells(i, "X").Value = "B"
        ActiveCell.FormulaR1C1 = "N/A"
        i = i + 1
    Case ws.Cells(i, "R").Value = 0 Or ws.Cells(i, "R").Value = "#N/A"
        ActiveCell.FormulaR1C1 = "N/A"
        i = i + 1
    End Select
    End If
Next

错误原因分析 #

1. 变量初始化错误：初始代码中i未赋值就直接引用单元格，导致无效引用；更新后代码i = i + 1初始值为1，但循环从第7行开始，行号完全不匹配。
2. 循环逻辑冲突：For Each cell遍历单元格的同时用i自增控制行号，极易出现行号错位，引发无效单元格引用。
3. Select Case语法错误：Select Case后应是要判断的表达式（如单元格值），而非条件语句；Case后应是匹配值，不能写条件判断。
4. 未限定工作表对象：Range和ActiveCell未指定工作表，可能误引用其他工作表的单元格。
5. 错误值判断错误：直接用= "#N/A"判断单元格错误值无效，需用IsError函数处理。

修复后的完整代码 #

Sub CALCULATE_GROSS_MARGIN()
    Dim i As Long, ws As Worksheet
    Set ws = ActiveWorkbook.Worksheets("Summary")
    
    Dim lastRow As Long
    ' 限定工作表获取最后一行，避免跨表引用错误
    lastRow = ws.Range("I" & ws.Rows.Count).End(xlUp).Row
    
    ' 从第7行开始循环，对应M列的起始行
    For i = 7 To lastRow
        ' 先判断L列是否为有效数值且大于0
        If IsNumeric(ws.Cells(i, "L").Value) And ws.Cells(i, "L").Value > 0 Then
            ws.Cells(i, "M").FormulaR1C1 = "=RC[-1]"
        Else
            ' 判断R列是否为有效数值且大于0，同时处理错误值
            If Not IsError(ws.Cells(i, "R").Value) And ws.Cells(i, "R").Value > 0 Then
                ' F列为"Removed"时，根据X列值分支处理
                If ws.Cells(i, "F").Value = "Removed" Then
                    Select Case ws.Cells(i, "X").Value
                        Case "L"
                            ws.Cells(i, "M").FormulaR1C1 = "你的毛利率计算公式" ' 替换为实际公式
                        Case "M"
                            ws.Cells(i, "M").FormulaR1C1 = "你的毛利率计算公式" ' 替换为实际公式
                        Case "R"
                            ws.Cells(i, "M").FormulaR1C1 = "你的毛利率计算公式" ' 替换为实际公式
                        Case "B"
                            ws.Cells(i, "M").Value = "N/A"
                        Case Else
                            ws.Cells(i, "M").Value = "N/A"
                    End Select
                Else
                    ' F列不为"Removed"时的默认处理，可按需补充
                    ws.Cells(i, "M").Value = "N/A"
                End If
            Else
                ' R列为0或错误值时赋值N/A
                ws.Cells(i, "M").Value = "N/A"
            End If
        End If
    Next i
End Sub

修复要点说明 #

• 统一行号控制：用For i = 7 To lastRow直接遍历目标行，避免循环逻辑冲突。
• 限定工作表对象：所有单元格操作都指定ws，确保引用正确工作表。
• 修正Select Case语法：先判断F列条件，再针对X列值做分支匹配，符合VBA语法规范。
• 处理错误值与非数值：用IsNumeric和IsError判断单元格有效性，避免比较时触发错误。
• 避免使用ActiveCell：直接指定目标单元格赋值或写入公式，提升代码稳定性。

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