Is it possible to use two different cell ranges in an if-statement and then have a result of that if-statement inserted into another range?

Code:

If Range("H3:H26").Value > Range("K3:K26") Then Range("N3:N26").Value = "Over"
ElseIf Range("H3:H26").Value < Range("K3:K26") Then Range("N3:N26").Value = "Under"
ElseIf Range("H3:H26").Value = Range("K3:K26") Then Range("N3:N26").Value = "Good"
Else: Range("N3:N26") = "No"
End If

I understand why having a two different ranges in an if-statement wouldn't work but I'm trying to find a way to have this code work without having multiple if statements for different rows

CodePudding user response：

You cannot use comparison operators with arrays in VBA. When you do Range.Value with a multicell range, you are creating a 2D array. Comparison Operators like > and < only work with individual values, not with arrays of values in VBA.

You need to loop through the rows, checking each cell from H3:H26 and K3:K26 as individual values.

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

You can use an If Statement or the much cleaner looking Select Case statement

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

You could also not need to loop or use any if statement by making use of the IF formula in excel.

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

CodePudding user response：

Compare Two Columns

Usage (The Calling Procedure(s))

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

Help (The Called Procedures)

• The first one is actually a method written as a function utilized in the TestChecks procedure.
• The second is called only by the first.

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