CODEWITHSUNDEEP
vbaexcel
Walshie1987
Walshie1987

Reputation: 443

Excel vba variable logic operator

I have a spreadsheet where the user can define some rules, but I'm struggling with getting the log to work. I want difference results based on what the user wants, but ideally I want to stay away from have to but multiple select cases in or if lines.

What I have Is this (Simplified - It's in a loop so the values will change for every row - I'm just using an example):

The operator can vary, it could be ">","=>" etc.

Field1Value = "Football"
Operator1 = "="
Rule1 = "Football"

If Evaluate(Field1Value & Operator1 & Rule1 ) Then

'Run My Code

End If

So in the above example the logic would be true and my code would run, but I'm getting a type mismatch error.

Has anyone any ideas?

Thanks Chris

********UPDATE********

Here's the full Code:

Workbooks(MasterWB).Activate

Sheets("Rules").Select

    NoRules = Sheets("Rules").Range("J6").End(xlDown).Row

    For a = 7 To NoRules

        Field1 = Workbooks(DataWB).Sheets(DataWS).Rows(1).Find(Workbooks(MasterWB).Sheets("Rules").Cells(a, 10), , xlValues, xlWhole).Column
        Operator1 = Sheets("Rules").Cells(a, 11)
        Rule1 = Sheets("Rules").Cells(a, 12)

        Operator = Sheets("Rules").Range("J5").Cells(a, 13)

        Field2 = Workbooks(DataWB).Sheets(DataWS).Rows(1).Find(Workbooks(MasterWB).Sheets("Rules").Cells(a, 14), , xlValues, xlWhole).Column
        Operator2 = Sheets("Rules").Cells(a, 15)
        Rule2 = Sheets("Rules").Cells(a, 16)

        HighlightColumn = Workbooks(DataWB).Sheets(DataWS).Rows(1).Find(Workbooks(MasterWB).Sheets("Rules").Cells(a, 17), , xlValues, xlWhole).Column
        HighlightColour = Workbooks(MasterWB).Sheets("Rules").Cells(a, 17).Interior.ColorIndex

        Workbooks(DataWB).Activate

        With Workbooks(DataWB).Sheets(DataWS)

            .Select

            Lastrow = .UsedRange.Rows.Count

            For b = 2 To Lastrow

            Field1Value = .Cells(b, Field1).Value

                If Evaluate(Field1Value & Operator1 & Rule1) Then

                        .Cells(b, HighlightColumn).Interior.ColorIndex = HighlightColour                      

                End If

            Next b

        End With


    Next a

Upvotes: 0

Views: 1194

Answers (3)

kitap mitap
kitap mitap

Reputation: 709

Try something like below:

Field1Value = "Football"
Operator1 = "="
Rule1 = "Football"

Dim x As Variant

strEvaluate = Chr(34) & Field1Value & Chr(34) & Operator1 & Chr(34) & Rule1 & Chr(34)

x = Evaluate("IF(" & strEvaluate & ",1,0)")

if x = 1 then
'Run your code
end if

Or instead of 1 and 0 use TRUE and FALSE. My language is not English. But I think this works also.

x = Evaluate("IF(" & strEvaluate & "," & True & ", " & False & ")")

if x = TRUE then
'Run your code
end if

Upvotes: 0

sgp667
sgp667

Reputation: 1875

Hold on I don't mean to steal Credit from FreeMan but following worked for me:

Sub test()
Dim Field1Value As String
Dim Operator1 As String
Dim Rule1 As String

Dim test As Range
Dim Passed As Boolean

    Field1Value = "Football"
    Operator1 = "="
    Rule1 = "Football"

    Passed = Evaluate("=" & """" & Field1Value & """" & Operator1 & """" & Rule1 & """")

    'Different way of achievieng same thing ignore this if you want to 
    Set test = Range("A1")
    test.Formula = "=" & """" & Field1Value & """" & Operator1 & """" & Rule1 & """"
    Passed = test.Value

    MsgBox Passed
End Sub

I tried it a with several values and operator and it works fine.

So I would only correct FreeMan by adding "=" & as first part of Evaluate.

So to make this more concise I'm saying write this : 

Evaluate("=" & """" & Field1Value & """" & Operator1 & """" & Rule1 & """")

Upvotes: 3

FreeMan
FreeMan

Reputation: 5687

Assuming that you're expecting "Football" = "Football" yields True, then give this a try:

If Evaluate("""" & Field1Value & """ " & Operator1 & " """ & Rule1 & """") Then

Note, I enclosed my strings in quotes - you need to pass those to Evaluate()

Actually, according to my reading of the MS Docs, I don't think this will work.

Evaluate(Name)

Name: A formula or the name of the object, using the naming convention of Microsoft Excel. The length of the name must be less than or equal to 255 characters.

What is the definition of "Football"? Is it a simple text string? My reading indicates that Evaluate() will execute a built in function or a UDF, but not a simple comparison.

Upvotes: 2

Related Questions