What is wrong with this For Loop code?

thardin

Board Regular
Joined
Sep 29, 2021
Messages
136
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

Spell Check in Excel
Press F7 to start spell check in Excel. Be careful, by default, Excel does not check Capitalized Werds (whoops)

jasonb75

Well-known Member
Joined
Dec 30, 2008
Messages
15,468
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.
 
Upvote 0

thardin

Board Regular
Joined
Sep 29, 2021
Messages
136
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.
 
Upvote 0

jasonb75

Well-known Member
Joined
Dec 30, 2008
Messages
15,468
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
 
Upvote 0

thardin

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

jasonb75

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

thardin

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

Forum statistics

Threads
1,186,365
Messages
5,957,438
Members
438,306
Latest member
Crystal_Blue

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