问如何在遍历多行时使用If-语句

EN

Sub Example()
    Dim i As Long
    For i = 3 To 26
        If Cells(i, "H").Value > Cells(i, "K").Value Then
            Cells(i, "N").Value = "Over"
        ElseIf Cells(i, "H").Value < Cells(i, "K").Value Then
            Cells(i, "N").Value = "Under"
        ElseIf Cells(i, "H").Value = Cells(i, "K").Value Then
            Cells(i, "N").Value = "Good"
        Else
            Cells(i, "N").Value = "No"
        End If
    Next
End Sub

Sub ExampleLoop()
    Dim i As Long
    For i = 3 To 26
        Select Case Cells(i, "H").Value
        Case Is > Cells(i, "K").Value
            Cells(i, "N").Value = "Over"
        Case Is < Cells(i, "K").Value
            Cells(i, "N").Value = "Under"
        Case Cells(i, "K").Value
            Cells(i, "N").Value = "Good"
        Case Else
            Cells(i, "N").Value = "No"
        End Select
    Next
End Sub

Sub ExampleFormula()
    Range("N3:N26").Formula = "=IFERROR(IF(H3>K3,""Over"",IF(H3<K3,""Under"",""Good"")),""No"")"
End Sub

Sub TestSimple()

    Dim ws As Worksheet: Set ws = ActiveSheet 'improve!
    Dim rg As Range: Set rg = ws.Range("K3:N26")
    CompareTwoColumns rg, 1, 4, 7, "Over", "Good", "Under", "No"

End Sub

Sub TestChecks()

    Dim ws As Worksheet: Set ws = ActiveSheet 'improve!
    Dim rg As Range: Set rg = ws.Range("K3:N26")
    
    Dim Compared As Boolean
    Compared = CompareTwoColumns(rg, 1, 4, 7, "Over", "Good", "Under", "No")
        
    If Not Compared Then Exit Sub

    MsgBox "The comparison finished successfully.", vbInformation

End Sub

Function CompareTwoColumns( _
    ByVal SourceRange As Range, _
    ByVal FirstColumn As Long, _
    ByVal SecondColumn As Long, _
    ByVal DestinationColumn As Long, _
    ByVal StringGT As String, _
    ByVal StringEQ As String, _
    ByVal StringLT As String, _
    ByVal StringElse As String) _
As Boolean
    Const ProcName As String = "CompareTwoColumns"
    On Error GoTo ClearError
    
    Dim rg As Range
    ' Source
    Set rg = SourceRange.Columns(FirstColumn)
    Dim fData() As Variant: fData = GetColumnRange(rg)
    ' Compare
    Set rg = SourceRange.Columns(SecondColumn)
    Dim sData() As Variant: sData = GetColumnRange(rg)
    ' Destination
    Set rg = SourceRange.Columns(DestinationColumn)
    Dim rCount As Long: rCount = rg.Rows.Count
    Dim dData() As String: ReDim dData(1 To rCount, 1 To 1)

    Dim fValue As Variant
    Dim sValue As Variant
    Dim r As Long
    Dim dString As String
    
    For r = 1 To rCount
        fValue = fData(r, 1)
        sValue = sData(r, 1)
        If VarType(fValue) = vbDouble Then
            If VarType(sValue) = vbDouble Then
                Select Case fValue
                    Case Is > sValue: dString = StringGT
                    Case sValue: dString = StringEQ
                    Case Is < sValue: dString = StringLT
                End Select
            End If
        End If
        If Len(dString) = 0 Then
            dData(r, 1) = StringElse
        Else
            dData(r, 1) = dString
            dString = vbNullString
        End If
    Next r
    
    SourceRange.Columns(DestinationColumn).Value = dData
    CompareTwoColumns = True

ProcExit:
    Exit Function
ClearError:
    MsgBox "' Run-time error '" & Err.Number & "':" & vbLf & vbLf _
        & Err.Description, vbCritical, ProcName
    Resume ProcExit
End Function


Function GetColumnRange( _
    ByVal rg As Range, _
    Optional ByVal ColumnNumber As Long = 1) _
As Variant
    If rg Is Nothing Then Exit Function
    If ColumnNumber < 1 Then Exit Function
    If ColumnNumber > rg.Columns.Count Then Exit Function
    
    With rg.Columns(ColumnNumber)
        If rg.Rows.Count = 1 Then
            Dim Data As Variant: ReDim Data(1 To 1, 1 To 1): Data(1, 1) = .Value
            GetColumnRange = Data
        Else
            GetColumnRange = .Value
        End If
    End With

End Function