emma_intertek
New Member
- Joined
- Sep 10, 2020
- Messages
- 10
- Office Version
- 2019
- 2016
- 2013
- Platform
- Windows
- 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:
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:
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
This one, first checks for blanks in a given range (Column A), these samples have not been checked yet:
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