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
Ah yes sorry, I have resolved this now. Yet I should've picked up on it sooner 😅

The beginning looks like this, so now x and the conditions are reset at the start of each loop.
VBA Code:
'Loop on range "1 is OK"
For Each cell In okRange
cell.Activate
x = Range("AD" & (ActiveCell.Row)).Value
condition1 = (x < 99.1)
condition2 = (x > 100.9)
 

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.

Mick_Webb

New Member
Joined
Apr 4, 2016
Messages
17
Office Version
  1. 2016
  2. 2013
  3. 2010
  4. 2007
Platform
  1. Windows
Hi,

Glad that you have been able to resolve your issue.


May I make a couple of suggestions and observations?

As was stated previously you should avoid using .Activate and .Select – use the range object, ‘cell’ in this instance, in place of ActiveCell as your expression. You are offsetting from ‘cell’ so the use of cell.Offset() is valid.

Look at defining named ranges for the relevant cells within your header block – e.g. for ‘1 is OK’ and ‘Fe2O3’ – and use these to define your run range and calculate your offsets – that way if the structure of your data block changes, e.g. a new column is inserted, your code will still work.

As there will always be an entry in ‘Fe2O3’ why not use this column to return the number of rows for your run range.

I am assuming that the only value seen in ‘1 is OK’ is 1, if so, why not look for instances where it is not 1.

If you wish to work ‘by best practice’ -

Use single lines to define variables – i.e. a Dim (Dimension) statement for each.

Use a naming convention – e.g. Reddick (Hungarian) – with variables and objects that you create.

Use the With statement when you use the same object in a block of code.

Your code will run and do the job without any of these but they will make it neater and easier to read by others, or by yourself if you return to it in 5 or 10 years time.
 

Watch MrExcel Video

Forum statistics

Threads
1,118,206
Messages
5,570,891
Members
412,345
Latest member
avelraza
Top