What is wrong with this For Loop code?

thardin

Board Regular
Joined
Sep 29, 2021
Messages
111
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 time
Select range and press Ctrl+Shift+2 to format cells as time. (Shift 2 is the @ sign).

jasonb75

Well-known Member
Joined
Dec 30, 2008
Messages
14,611
Office Version
  1. 365
Platform
  1. Windows
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.
 

thardin

Board Regular
Joined
Sep 29, 2021
Messages
111
Office Version
  1. 365
Platform
  1. Windows
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.
 

jasonb75

Well-known Member
Joined
Dec 30, 2008
Messages
14,611
Office Version
  1. 365
Platform
  1. Windows
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
 

thardin

Board Regular
Joined
Sep 29, 2021
Messages
111
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

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
 

jasonb75

Well-known Member
Joined
Dec 30, 2008
Messages
14,611
Office Version
  1. 365
Platform
  1. Windows
Oops, try deleting the first Then in the edited code line, it shouldn't be there.
 

thardin

Board Regular
Joined
Sep 29, 2021
Messages
111
Office Version
  1. 365
Platform
  1. Windows
So I tried taking out the "With Activesheet" but that still did nothing either
 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,151,962
Messages
5,767,344
Members
425,405
Latest member
jaskimo

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
Top