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

emma_intertek

New Member
Joined
Sep 10, 2020
Messages
10
Office Version
  1. 2019
  2. 2016
  3. 2013
Platform
  1. Windows
  2. MacOS
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
 

Some videos you may like

Excel Facts

Move date out one month or year
Use =EDATE(A2,1) for one month later. Use EDATE(A2,12) for one year later.

Peter_SSs

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
47,474
Office Version
  1. 365
Platform
  1. Windows
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
 

emma_intertek

New Member
Joined
Sep 10, 2020
Messages
10
Office Version
  1. 2019
  2. 2016
  3. 2013
Platform
  1. Windows
  2. MacOS
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 :)
 

mole999

Moderator
Joined
Oct 23, 2004
Messages
10,524
Office Version
  1. 2019
  2. 2016
  3. 2013
Platform
  1. Windows

ADVERTISEMENT

can you x = "" at the end of each loop ?
 

Peter_SSs

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
47,474
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

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.
 

Mick_Webb

New Member
Joined
Apr 4, 2016
Messages
17
Office Version
  1. 2016
  2. 2013
  3. 2010
  4. 2007
Platform
  1. Windows
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]
 

emma_intertek

New Member
Joined
Sep 10, 2020
Messages
10
Office Version
  1. 2019
  2. 2016
  3. 2013
Platform
  1. Windows
  2. MacOS
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?
 

Mick_Webb

New Member
Joined
Apr 4, 2016
Messages
17
Office Version
  1. 2016
  2. 2013
  3. 2010
  4. 2007
Platform
  1. Windows
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.​
 

Watch MrExcel Video

Forum statistics

Threads
1,118,134
Messages
5,570,363
Members
412,320
Latest member
sixnine0312
Top