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 :)
 
I tried putting a watch on everything. The watch picks up the correct value every time.
But it is still picking value from sample 1 or 2 or other, I don't understand why.

Capture7.PNG
 
Upvote 0

Excel Facts

What did Pito Salas invent?
Pito Salas, working for Lotus, popularized what would become to be pivot tables. It was released as Lotus Improv in 1989.
Welcome to the MrExcel board!

Any chance that we could have a small set of sample data with XL2BB and a complete set of code to test with?

BTW, are you aware that with your code at the bottom below, okRange and condition1 are actually declared as Variant (the default) and not Range and Boolean as I suspect that you think?
For example, if you want to declare both condition1 and condition2 as Boolean, you need to do it like this
VBA Code:
Dim condition1 As Boolean, condition2 As Boolean

VBA 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
 
Upvote 0
Hey there,
I posted about this a few days ago, you can find the thread here. Trying things again...

Excel doesn't seem to be picking up the correct cell/value each time this runs.
I'm trying to set x as the value of the cell in column AD for each row in my range.

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

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

Dim condition1 As Boolean, 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 (cell = "QC=") = True Then
    Exit For
    Else
    
        'Is cell empty? If yes, check results and apply foramatting.
        If cell = " " Or IsEmpty(cell) = True Or cell = "" 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
.........

But this keeps happening:
Capture.PNG

The range expression changes each time, but x remains the value of the last row I clicked before running sub.

Any ideas? Thank you so much :)
 
Upvote 0
I posted about this a few days ago, you can find the thread here.
So I have merged them - refer to #12 of the Forum Rules and No. 6 of the Guidelines

The issue is that whilst you told readers of the new thread about the old one, readers of the old one do not know about the new one so may waste effort in trying to develop further suggestions there.
 
Upvote 0
The way I understand this, but correct me if I am wrong - every row represents the results from a sample and within each sample you wish to compare the Fe203 (G:SampleRow) with the Total % Mass (AD:SampleRow) for that sample. If I am correct why are you setting your condition flags based around the result of the Total % Mass for the row (Sample) that is current when the procedure is run and then taking those flags into the loop? You need to do an actual comparison within your loop - i.e. within the row (Sample) that is the current element of the loop, using the values for that row.


:

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

[/QUOTE]
 
Upvote 0
Hey Mick, that sounds about right.

I activate each cell at the start of the loop, so that I can work with the results in the relative sample row. Dim x as Variant.
I use cell.offset for Fe2O3 so I can then apply conditional formatting.

Am I forgetting something?
 
Upvote 0
Hi Emma,

OK, let’s talk through an example –

Your cursor is at A15 and you run your procedure –

Step 1 – Sets your run range.​
Step 2 – The value at AD:15, the Total % Mass for the active row is returned to the variable x, this being 99.059 in the data you have posted.​
Step 3 – The variable condition1 is returned as -1 (True) as x is less than 99.1.​
Step 4– The variable condition2 is returned as 0 (False) as x is not greater than 100.9.​
Step 5 – You loop down the run range – all the while you are doing this​

Condition1 = True​
Condition2 = False​
X still = 99.059, but you do not use this​
As they have been set in regard to A15 and have not been changed!​
So, for each row (Sample) you are evaluating the Fe2O3 (Column G) value against the Total % Mass in row (Sample) 15. As you loop down the rows, you need to pick up the value for the Total % Mass (Column AD) for that row (Sample) and evaluate the Fe2O3 (Column G) for that row (Sample) against it.​
 
Upvote 0

Forum statistics

Threads
1,212,928
Messages
6,110,734
Members
448,294
Latest member
jmjmjmjmjmjm

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