(Solved) – EXCEL Evaluate function inconsistent result

  • by

I have one data worksheet which is as per below format (100 cols)

enter image description here

then I have another file containing some rules (multiple rules for each column in data file). I formulated vba conditions for every rule; for example, one rule was to check column A of data file should have only BP or Trip value for all rows (50K rows). So I translated that to below VBA

DataWB.Worksheets(1).Cells(J,X) = “BP” OR DataWB.Worksheets(1).Cells(J,X) = “Trip”

enter image description here

validate button code

        f = Trim(ThisWorkbook.Worksheets(1).Cells(2, 3))
        f = Replace(f, "J", 2)
        f = Replace(f, "X", 1)
        Debug.Print Application.Evaluate(f)

now problem is that evaluate throws type mismatch and if I use CBOOL it always return TRUE. I tried different variations too (like adding = or ? prefix) but nothing seem working. Any help will be highly appreciated.

enter image description here

Leave a Reply

Your email address will not be published. Required fields are marked *