Retrieving a comparison operator from cell

user_10544123 0
Viewed 39

I am trying to do a vba code for excel where I can retrieve the comparison operator(e.g. <, <= etc.) from the excel sheet. What I am trying to do give a score based on the value and the bands being key in.

I wanted to do something like this in the code:

Sample data:

cell A1 = 80(input)
cell A4 = "<"
cell B4 = 75
cell C4 = "="
cell D4 = 75
cell E4 = ">"
cell F4 = 75

Example of the code I wanted to do:

dim score as integer
dim result as integer 
score = range("A1").value

methodoperatorb1 = range("A4").value
methodoperatorb2 = range("C4").value
methodoperatorb3 = range("E4").value

band1 = range("B4").value
band2 = range("D4").value
band3 = range("F4").value

if score (methodoperator1)(band1) then result = 1
elseif score (methodoperator2)(band2) then result = 2
else result = 3

Sorry for the bad example and really hope someone can help me with this problem.

vba excel
1 Answers

You could use Evaluate to evaluate the expressions like this:

Sub foo()

    Dim score As Integer
    score = Range("A1").Value

    methodoperatorb1 = Range("A4").Value
    methodoperatorb2 = Range("C4").Value
    methodoperatorb3 = Range("E4").Value

    band1 = Range("B4").Value
    band2 = Range("D4").Value
    band3 = Range("F4").Value

    Dim result As Integer
    If Application.Evaluate(score & methodoperatorb1 & band1) Then
        result = 1
    ElseIf Application.Evaluate(score & methodoperatorb2 & band2) Then
        result = 2
    Else
        result = 3
    End If
    MsgBox result
End Sub

Note that this will only work if the total length of the expression is under 256 characters.

user_36119890
Related