What is wrong with this For Loop code?

thardin

Board Regular
Joined
Sep 29, 2021
Messages
137
Office Version
  1. 365
Platform
  1. Windows
I am trying to loop through each cell in Column U from U4 to last row to check if the percentage in the cell is greater than -.050%. In a previous macro, the format of the cells in column U is set to "0.000%." This Macro isn't working correctly.
Is it because I set the Max variable in the wrong format?
Thanks


Sub CheckII()

Dim ws As Worksheet
Dim Cell As Range
Dim lr As Long

Dim iViolations As Integer
Dim Max As Double
Max = -0.05 / 100

iViolations = 0

For Each ws In ActiveWorkbook.Worksheets
With ActiveSheet
lr = Cells(Rows.Count, "B").End(xlUp).row
For Each Cell In ws.Range("U4:U" & lr)
If Cell.Value > Max Then
'Cell.EntireRow.Interior.ColorIndex = 3
iViolations = iViolations + 1
End If
Next Cell
End With
Next ws

MsgBox iViolations


End Sub
 

Excel Facts

Format cells as date
Select range and press Ctrl+Shift+3 to format cells as date. (Shift 3 is the # sign which sort of looks like a small calendar).
How do you define 'Not working correctly'?

That is a rather ambiguous term if you don't tell us what it is doing versus what is expected.
 
Upvote 0
How do you define 'Not working correctly'?

That is a rather ambiguous term if you don't tell us what it is doing versus what is expected.
When the msgbox pops up it says the wrong number of violations. (it is supposed to say 0)
Somehow it's counting all the blank rows underneath the dataset, because all those rows are highlighting in red.
 
Upvote 0
If they are blank but not empty then that's the root of the problem, a text null string has a higher value than any number so will be counted as greater than max.
Try one small change and see if it works.
VBA Code:
If Cell.Value > Max Then And IsNumeric(Cell.Value) Then
 
Upvote 0
This is what the worksheet template looks like and all those white rows are getting highlighted in red when I run the macro

1638376814302.png
 
Upvote 0
Oops, try deleting the first Then in the edited code line, it shouldn't be there.
 
Upvote 0
So I tried taking out the "With Activesheet" but that still did nothing either
 
Upvote 0

Forum statistics

Threads
1,212,927
Messages
6,110,710
Members
448,293
Latest member
jin kazuya

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