How can I read a comparison operator from a cell and use it in an If...Then...Else statement?

I've searched all over MSDN and couldn't find any info about this. I have a "Settings" sheet that controls my application. This sheet has a range of cells that store comparison operators such as <= and > for use in a procedure.

I have a list of fruits and a list of prices for how much each fruit was sold. I want to see how many apples have been sold for a price less than or equal to $10.

The previous version of my program used the WorksheetFunction.CountIfs method, and it basically read the operators from a worksheet into an array and used them like this:

Sub BuiltInCountIfsUsingArray()

    Dim rngOperator As Range
    Dim avOperator As Variant

    Set rngOperator = [A1:B1]

    avOperator = rngOperator

    With WorksheetFunction
        lResult = .CountIfs(rngFruits, _
            avOperator(1, 1) & "apple", _
            rngPrice, _
            avOperator(1, 2) & 10)
    End With

End Sub

avOperator(1,1) is read from A1, which contains =

avOperator(1,2) is read from B1, which contains <=

so in VBA,

avOperator(1,1) returns the value "="

avOperator(1,2) returns the value "<="

so therefore the above code is equivalent to:

Sub BuiltInCountIfs()

    With WorksheetFunction
        lResult = .CountIfs(rngFruits, _
            "=" & "apple", _
            rngPrice, _
            "<=" & 10)
    End With

End Sub

and the CountIfs function works fine with this.

My question is: how can I use the same kind of method of storing the comparison operators, but then using them in an If...Then...Else statement?

Basically, I want to go from this:

Sub MyCountIfs()

    Dim lRow As Long
    Dim lCounter As Long
    Dim lResult As Long

    For lRow = 1 To 10
        If rngFruits(lRow, 1) = "apple" _
            And rngPrices(lRow, 1) <= 10 Then

            lCounter = lCounter + 1

        End If
    Next lRow

    lResult = lCounter

End Sub

into something like this:

Sub MyCountIfsUsingArray()

    Dim lRow As Long
    Dim lCounter As Long
    Dim lResult As Long

    Dim rngOperator As Range
    Dim avOperator As Variant

    Set rngOperator = [A1:B1]

    avOperator = rngOperator

    For lRow = 1 To 10
        If rngFruits(lRow, 1) & avOperator(1, 1) & "apple" _
 开发者_C百科           And rngPrices(lRow, 1) & avOperator(1, 2) & 10 Then

            lCounter = lCounter + 1

        End If
    Next lRow

    lResult = lCounter

End Sub

if this is impossible with an array, reading the rngOperator directly would be good, too. Something like this maybe:

Sub MyCountIfsUsingRange()

    Dim lRow As Long
    Dim lCounter As Long
    Dim lResult As Long

    Dim rngOperator As Range
    Dim avOperator As Variant

    Set rngOperator = [A1:B1]

    For lRow = 1 To 10
        If rngFruits(lRow, 1) & rngOperator(1, 1) & "apple" _
            And rngPrices(lRow, 1) & rngOperator(1, 2) & 10 Then

            lCounter = lCounter + 1

        End If
    Next lRow

    lResult = lCounter

End Sub

Thanks in advance!

If you included a reference to the Microsoft Access x.x Object Library, you could say:

    If Eval("'" & rngFruits(lRow, 1) & "'" & avOperator(1, 1) & "'apple' AND " _
         & rngPrices(lRow, 1) & avOperator(1, 2) & 10) Then

I am not sure why excel does not have Eval, but I am testing on an early version, so you may wish to check. Note that Evaluate is not the same thing at all. I am not at all sure that this is a good idea, but it is there, FWIW.

You can write your own function to do this

Something like

Function CompareTest(v1 As Variant, Operator As String, v2 As Variant) As Boolean
    Select Case Operator
        Case "=": CompareTest = v1 = v2
        Case "<>": CompareTest = v1 <> v2
        Case ">": CompareTest = v1 > v2
        Case ">=": CompareTest = v1 >= v2
        Case "<": CompareTest = v1 < v2
        Case "<=": CompareTest = v1 <= v2
        Case <insert any other operator you may need> 
        Case Else: <handle the invalid operator case as you see fit>
    End Select
End Function

Then your code becomes

For lRow = 1 To 10
    If CompareTest(rngFruits(lRow, 1), rngOperator(1, 1), "apple") And _
       CompareTest(rngPrices(lRow, 1), rngOperator(1, 2), 10) Then

        lCounter = lCounter + 1

    End If
Next lRow

i just realized that using a function within the loop destroys the performance, so i'll just do a select case for each operator before the loop is run. thanks guys, i appreciate your time an effort and i learned something new today :)

i'm dumb, i forgot to mention that the first operator is always "=", so the user will only specify the second one.

Sub MyCountIfsUsingCases()

    Dim lRow As Long
    Dim lCounter As Long
    Dim lResult As Long

    Dim rngOperator As Range
    Dim avOperator As Variant

    Set rngOperator = [A1:B1]

    avOperator = rngOperator

    Select Case avOperator(1, 2)
        Case "<="
            For lRow = 1 To 10
                If rngFruits(lRow, 1) = "apple" _
                    And rngPrices(lRow, 1) <= 10 Then

                    lCounter = lCounter + 1

                End If
            Next lRow

        Case ">="
            For lRow = 1 To 10
                If rngFruits(lRow, 1) = "apple" _
                    And rngPrices(lRow, 1) >= 10 Then

                    lCounter = lCounter + 1

                End If
            Next lRow

        'Case "<", etc...

    End Select

    lResult = lCounter

End Sub