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

Excel Facts

Does the VLOOKUP table have to be sorted?
No! when you are using an exact match, the VLOOKUP table can be in any order. Best-selling items at the top is actually the best.
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.
 
Upvote 0

Forum statistics

Threads
1,212,933
Messages
6,110,752
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