VBA to check each row in a column is within a preferred range?

[thardin]

This is an example of a report I work with.
How would you write a "For each row" statement to make sure the ID's percentage is within preferred range based on the data in column E and V and the limits in the table below.
And if everything is in range: MSbox "no violations"

Note: the amount of rows change on a daily basis.

Thanks

 

[Sahak]

Hi Thardin,

I will suggest inserting a column after column "V" (or any empty column) & in "W1"put formula

=IF(VLOOKUP(E1,$D$14:$E$16,2,0)*100<V1,"OVER","OK")

& copy down. This formula will show the correct answer any time & you can filter "OVER" to see in which rows you have violations.

 

[tonyyy]

You might consider this vba approach...

Sub ArrayLoop()
Dim arr As Variant
Dim i As Long
Dim Violations As String

arr = ActiveSheet.UsedRange
For i = 2 To UBound(arr)
    If arr(i, 5) < arr(i, 22) Then Violations = Violations & vbCrLf & arr(i, 4)
Next i
If Violations = "" Then
    MsgBox "No violations"
Else
    MsgBox "Please note the following violations: " & vbCrLf & Violations
End If
End Sub

Cheers,

Tony

 

[thardin]

Hi Thardin,

I will suggest inserting a column after column "V" (or any empty column) & in "W1"put formula

=IF(VLOOKUP(E1,$D$14:$E$16,2,0)*100<V1,"OVER","OK")

& copy down. This formula will show the correct answer any time & you can filter "OVER" to see in which rows you have violations.

I can't do this because I can't change the template of the Worksheet

 

[Sahak]

How about using the last empty column for formulas, after finding out violations you can delete that column?

 

[thardin]

I have created this code that successfully performs the task that I want. However, I got it to work only for the first row, "E5" just to see if it worked.
I used this code because the ID table on the bottom usually isn't there. I included that table so it can be in the picture and thought it would make it easier to understand what I was doing.
This code checks if each ID's Load Percent is within the maximum allowable range, using the data in E5 and V5 in the dataset.


Sub TestII()
Dim Sheet As Worksheet
Dim cell As Range
Dim lr As Long
Dim i As Integer


If (Range("E5") = "462266446" And Range("V5") < 3.75) Then
MsgBox "Good"

ElseIf (Range("E5") = "89008P588" And Range("V5") < 3.75) Then
MsgBox "Good"

ElseIf (Range("E5") = "89008P638" And Range("V5") < 4.5) Then
MsgBox "Good"
Else
MsgBox "Bad"
End If
End Sub
-------------------------------------------------------------------------------------------------------------------------------------------------------------------
How do I do this through the entire used range though(in this picture, range A5:AB7)?-

I attempted to use the code below to perform the task through each row, but to highlight the violations instead of message boxes, but it doesn't quite work.
It doesn't work because when there's multiple violations, it doesn't highlight them all, only the 1st one. Also, when there are no violations, a Message box pops up and I have to click "Ok" multiple times depending on the amount of rows.

What should I change in order to perform a successful loop?

Thanks

Sub Test()
Dim lr As Long
Dim WS As Worksheet
Dim r As Long


lr = Cells(Rows.Count, "C").End(xlUp).Row

For r = 5 To lr

If (Columns(5).Cells(r).Value = "462266446" And Columns(22).Cells(r).Value > 3.75) Then
Rows(5).Interior.ColorIndex = 3

ElseIf (Columns(5).Cells(r).Value = "89008P588" And Columns(22).Cells(r).Value > 3.75) Then
Rows(5).Interior.ColorIndex = 3

ElseIf (Columns(5).Cells(r).Value = "89008P638" And Columns(22).Cells(r).Value > 4.5) Then
Rows(5).Interior.ColorIndex = 3
Else
MsgBox "No Violations"
End If
Next r

End Sub