如何在遍历多行时使用If语句？多单元格区域条件判断实现

你的原代码无法运行，核心原因是直接用Range对象的Value属性做比较时，返回的是二维数组，VBA不支持直接对两个数组做整体的大于/小于/等于判断，必须逐元素处理。下面提供三种无需重复写多个If语句的可行方案：

方案1：数组批量处理（推荐，效率最高） #

把数据读取到内存数组中处理，减少和工作表的交互次数，大幅提升运行速度，尤其适合数据量较大的场景：

Sub CompareRangesWithArray()
    Dim ws As Worksheet
    Dim hVals As Variant, kVals As Variant, resultVals As Variant
    Dim i As Long
    
    '指定目标工作表，建议替换为实际表名，比如ThisWorkbook.Worksheets("Data")
    Set ws = ActiveSheet
    
    '将H、K列数据读入数组
    hVals = ws.Range("H3:H26").Value
    kVals = ws.Range("K3:K26").Value
    
    '初始化结果数组，尺寸与原数据一致
    ReDim resultVals(1 To UBound(hVals, 1), 1 To 1)
    
    '遍历数组逐行判断
    For i = 1 To UBound(hVals, 1)
        If hVals(i, 1) > kVals(i, 1) Then
            resultVals(i, 1) = "Over"
        ElseIf hVals(i, 1) < kVals(i, 1) Then
            resultVals(i, 1) = "Under"
        ElseIf hVals(i, 1) = kVals(i, 1) Then
            resultVals(i, 1) = "Good"
        Else
            resultVals(i, 1) = "No"
        End If
    Next i
    
    '将结果批量写入N列
    ws.Range("N3:N26").Value = resultVals
End Sub

方案2：逐单元格遍历（逻辑直观，适合新手） #

直接逐行遍历每个单元格，逻辑简单易懂，适合小数据量场景：

Sub CompareCellsOneByOne()
    Dim ws As Worksheet
    Dim rowNum As Long
    
    Set ws = ActiveSheet
    
    '遍历3到26行
    For rowNum = 3 To 26
        Select Case ws.Cells(rowNum, "H").Value
            Case Is > ws.Cells(rowNum, "K").Value
                ws.Cells(rowNum, "N").Value = "Over"
            Case Is < ws.Cells(rowNum, "K").Value
                ws.Cells(rowNum, "N").Value = "Under"
            Case ws.Cells(rowNum, "K").Value
                ws.Cells(rowNum, "N").Value = "Good"
            Case Else
                ws.Cells(rowNum, "N").Value = "No"
        End Select
    Next rowNum
End Sub

这里用Select Case替代了多层If-Else，代码结构更清晰。

方案3：调用工作表函数批量填充（最简洁） #

直接给目标区域设置Excel公式，让Excel自动计算每行结果，无需VBA循环：

Sub UseExcelFormula()
    Dim ws As Worksheet
    
    Set ws = ActiveSheet
    
    '给N3:N26区域设置嵌套IF公式
    ws.Range("N3:N26").Formula = "=IF(H3>K3,""Over"",IF(H3<K3,""Under"",IF(H3=K3,""Good"",""No"")))"
    
    '如果需要把公式转换为静态值，取消下面一行注释
    'ws.Range("N3:N26").Value = ws.Range("N3:N26").Value
End Sub

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