Boolean conditions not working?

emma_intertek

New Member
Joined
Sep 10, 2020
Messages
10
Office Version
  1. 2019
  2. 2016
  3. 2013
Platform
  1. Windows
  2. MacOS
Hiya, I work in a laboratory and I write macros that find anomaly samples on a spreadsheet of results. I've been sitting on this one for a while and I cannot find the error.

This one, first checks for blanks in a given range (Column A), these samples have not been checked yet:

Capture.PNG


If it finds an un-checked sample, it compares Fe2O3 (Column G) with the Total % Mass (Column AD). I've done this using Boolean conditions/inequalities and (ActiveCell.Row) but it doesn't seem to run the same (or properly) each time. This is how I've done it:

Code:
Dim okRange, cell As Range
Set okRange = Range("A15:A1000")

Dim x As Variant
x = Range("AD" & (ActiveCell.Row)).Value

Dim condition1, condition2 As Boolean
condition1 = x < 99.1
condition2 = x > 100.9

'Loop on range "1 is OK"
For Each cell In okRange
cell.Activate

    'Has loop reached the end?
    If (ActiveCell = "QC=") = True Then
    Exit For
    Else
    
        'Is cell empty? If yes, check results and apply foramtting.
        If ActiveCell = " " Or IsEmpty(ActiveCell) = True Then
        ActiveCell.Offset(0, 6).Select
        ActiveCell.FormatConditions.Delete
        If ActiveCell.Value < 64 And (condition1 = True Or condition2 = True) Then
        ActiveCell.FormatConditions.Add Type:=xlCellValue, Operator:=xlLess, _
        Formula1:=64
......

The conditions sometimes don't work, claiming False when actually true, etc.
Also when I step through, excel sometimes fails to find the correct "ActiveCell" or active row, I am not sure why.

Any help would be appreciated, hmu if you require more details :)
 

Some videos you may like

Excel Facts

Excel Joke
Why can't spreadsheets drive cars? They crash too often!

rondeondo

Board Regular
Joined
Aug 15, 2012
Messages
156
VBA Code:
If ActiveCell = " " Or IsEmpty(ActiveCell) = True Then
do you have spaces in the empty cells? you might want to check for "" to be counted as empty too.

To speed up your code and make it a little more robust, don't activate each cell as you go through the data.
In stead of referring to active cell just refer to 'cell' within the code. You are already doing a for loop with for each cell in OKrange
 

emma_intertek

New Member
Joined
Sep 10, 2020
Messages
10
Office Version
  1. 2019
  2. 2016
  3. 2013
Platform
  1. Windows
  2. MacOS
VBA Code:
If ActiveCell = " " Or IsEmpty(ActiveCell) = True Then
do you have spaces in the empty cells? you might want to check for "" to be counted as empty too.

To speed up your code and make it a little more robust, don't activate each cell as you go through the data.
In stead of referring to active cell just refer to 'cell' within the code. You are already doing a for loop with for each cell in OKrange

I've added that just in case, this doesn't seem to be the problem though.

I've replaced ActiveCell where I can, I'm still using it in this macro a lot for cells offset from okRange, etc..
I understand this way is slightly less efficient, it's just a matter of getting it to run correctly :)
 

emma_intertek

New Member
Joined
Sep 10, 2020
Messages
10
Office Version
  1. 2019
  2. 2016
  3. 2013
Platform
  1. Windows
  2. MacOS
x = Total % Mass (value)
When I step through and check, excel does not always find the correct value/cell.
Dim x As Variant x = Range("AD" & (ActiveCell.Row)).Value

I cannot change to cell.Row, or can I? Comes back as error - object variable not set.
Thanks for the help 😅
 

rondeondo

Board Regular
Joined
Aug 15, 2012
Messages
156

ADVERTISEMENT

Hi Emma

can you give the values in the relevant cells where there are issues with the results
 

emma_intertek

New Member
Joined
Sep 10, 2020
Messages
10
Office Version
  1. 2019
  2. 2016
  3. 2013
Platform
  1. Windows
  2. MacOS

ADVERTISEMENT

Hi Emma

can you give the values in the relevant cells where there are issues with the results

These values are in column AD and they look like this :))
Capture.PNG
 

rondeondo

Board Regular
Joined
Aug 15, 2012
Messages
156
you have the lines:
VBA Code:
condition1 = x < 99.1
condition2 = x > 100.9
then later:
VBA Code:
If ActiveCell.Value < 64 And (condition1 = True Or condition2 = True) Then
What happens with the value is between these values?

put a watch on the variable x to confirm it's picking up the correct value?
 

emma_intertek

New Member
Joined
Sep 10, 2020
Messages
10
Office Version
  1. 2019
  2. 2016
  3. 2013
Platform
  1. Windows
  2. MacOS
Hey,

If value is between those constants, sample is okay, I mark it as 1 in column A.

I tried stepping through watching x, what is coming up on the watches menu is different from what I see when I put my cursor over parts of the code.
It seems excel is following this?
Sample 8 in given example should be 100.1 and therefore should pass because both conditions are False, but it is showing True.

Capture3.PNG

Capture4.PNG


Hope that makes sense. Thank you!
 

rondeondo

Board Regular
Joined
Aug 15, 2012
Messages
156
I wonder if it's worth putting brackets around the assignment
VBA Code:
condition1 = (x <99.1)
It shouldn't make a difference.
It's odd that the watch is showing a different value to the mouse-over in the code.
With these type of issues I put watches on everything and step through the code. So put a watch on the cell ref and x and conditions 1 and 2 and also check with the mouse over the variables.
Note that the variable will show the old value until the code execution step has gone past.
 

Watch MrExcel Video

Forum statistics

Threads
1,118,210
Messages
5,570,918
Members
412,349
Latest member
big_words
Top