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 :)
 

Excel Facts

Easy bullets in Excel
If you have a numeric keypad, press Alt+7 on numeric keypad to type a bullet in Excel.
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
 
Upvote 0
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 :)
 
Upvote 0
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 ?
 
Upvote 0
Hi Emma

can you give the values in the relevant cells where there are issues with the results
 
Upvote 0
Hi Emma
yes change that to cell and see what happens
 
Upvote 0
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?
 
Upvote 0
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!
 
Upvote 0
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.
 
Upvote 0

Forum statistics

Threads
1,212,928
Messages
6,110,737
Members
448,295
Latest member
Uzair Tahir Khan

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top