在含ListObjects的工作表中编写带IF逻辑的Excel VBA宏求助

首先，你的测试代码无法正常运行可能存在几个关键问题，以下是针对性的修正和完整需求实现：

一、修正测试代码（解决当前运行问题） #

Sub Test()
    Dim ws As Worksheet
    Dim tbl As ListObject
    Dim rng As Range
    Dim cel As Range
    
    ' 直接指定目标工作表，避免依赖ActiveSheet导致的错误
    Set ws = ThisWorkbook.Worksheets("CommissionVoice")
    Set tbl = ws.ListObjects("CommissionVoice")
    
    ' 检查表格是否有数据行，避免DataBodyRange为空时触发报错
    If tbl.DataBodyRange Is Nothing Then
        MsgBox "表格中没有数据行", vbExclamation
        Exit Sub
    End If
    
    Set rng = tbl.ListColumns(12).DataBodyRange ' L列为第12列
    
    For Each cel In rng
        ' 使用vbTextCompare忽略大小写，匹配所有大小写变体的"No Pay"
        If InStr(1, cel.Value, "No Pay", vbTextCompare) > 0 Then
            cel.Offset(0, -1).Value = "OK" ' 写入K列（L列左移1列）
        End If
    Next cel
End Sub

核心问题修正说明： #

• 指定固定工作表：原代码依赖ActiveSheet，若当前活动表不是「CommissionVoice」，会找不到目标表格，直接指定工作表更可靠。
• 空数据判断：如果表格无数据行，DataBodyRange会返回Nothing，直接遍历会触发运行时错误，增加判断可避免崩溃。
• 大小写不敏感匹配：InStr默认区分大小写，原代码的"No pay"与目标文本"No Pay"大小写不一致，添加vbTextCompare参数可忽略大小写，确保所有变体都能匹配。

二、完整需求实现（包含计算逻辑） #

根据你的需求，检查L列是否包含"No Pay"或"Below Target"，满足条件时用K列（整数）乘以E列（百分比），以下是完整实现（示例将结果写入M列）：

Sub CalculateCommission()
    Dim ws As Worksheet
    Dim tbl As ListObject
    Dim row As ListRow
    Dim statusVal As String
    Dim kVal As Double
    Dim eVal As Double
    
    Set ws = ThisWorkbook.Worksheets("CommissionVoice")
    Set tbl = ws.ListObjects("CommissionVoice")
    
    If tbl.DataBodyRange Is Nothing Then
        MsgBox "表格中没有数据行", vbExclamation
        Exit Sub
    End If
    
    ' 遍历表格每一行，通过列索引直接定位单元格，避免Offset偏移错误
    For Each row In tbl.ListRows
        statusVal = row.Range(tbl.ListColumns(12).Index).Value ' 获取L列状态值
        
        ' 同时匹配两个目标条件
        If InStr(1, statusVal, "No Pay", vbTextCompare) > 0 Or _
           InStr(1, statusVal, "Below Target", vbTextCompare) > 0 Then
           
            ' 获取K列（第11列）和E列（第5列）的数值
            kVal = row.Range(tbl.ListColumns(11).Index).Value
            eVal = row.Range(tbl.ListColumns(5).Index).Value
            
            ' 执行计算并写入M列（第13列），可根据需求修改目标列索引
            row.Range(tbl.ListColumns(13).Index).Value = kVal * eVal
        End If
    Next row
End Sub

完整实现的优势： #

• 按行遍历更直观：使用ListRow对象遍历表格行，直接通过列索引定位单元格，避免Offset偏移导致的列定位错误。
• 覆盖多条件需求：同时匹配"No Pay"和"Below Target"两个条件，完全符合需求。
• 数值类型安全：用Double类型存储数值，避免整数溢出或百分比计算的精度问题。

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