CODEWITHSUNDEEP
vbaexcel
user3758610
user3758610

Reputation: 15

Find consecutive cells based on a value

I have a bank of data that is updated monthly. I need to analyze said data to determine first all values below 64, then I need to look for two consecutive instances of this event. Once found, I would like a value returned to indicate this vendor is a problem.

The second piece of the puzzle is that I also need to analyze the data for three consecutive instance of values above 64 if the vendor has been labeled as problem.

I am not sure if VBA is appropriate or a formula approach will work. I am able to write most VBA code and work with basic formula. Example here:
http://s28.postimg.org/im5rq83al/Untitled.png

Upvotes: 1

Views: 3580

Answers (2)

Chrismas007
Chrismas007

Reputation: 6105

This code assumes your data starts in cell B4 (such as your picture suggests) and outputs a problem if under 64 is found twice in a row. However, if above 64 is found 3 times in a row AFTER the issue, it returns "N" for no problem:

Sub VendorProblems()
    Dim cl As Long, LastRow As Long, i As Long, LastCL As Long, Problem As String
        LastRow = ActiveSheet.Range("A" & Rows.Count).End(xlUp).Row
        LastCL = ActiveSheet.Cells(4, Columns.Count).End(xlToLeft).Column - 1
    For i = 4 To LastRow
        Problem = ""
        For cl = 2 To LastCL
            Set r1 = Cells(i, cl)
            Set r2 = r1.Offset(0, 1)
            Set r3 = r1.Offset(0, 2)
            If r1.Value < 64 And r2.Value < 64 Then
            Problem = "Y"
            Else
            End If
            If r1.Value >= 64 And r2.Value >= 64 And r3.Value >= 64 Then
            Problem = "N"
            Else
            End If
        Next cl
        If Problem = "Y" Then
        Cells(i, LastCL + 1).Value = "Y"
        Else
        Cells(i, LastCL + 1).Value = "N"
        End If
    Next i
End Sub

Upvotes: 2

MikeG
MikeG

Reputation: 545

Here is the simplest solution pasting these formulas in a single column. If the first formula column is False, you can just ignore the second.

Two consecutive values below 64:

=OR( A1 + B1 < 128 , B1 + C1 < 128, etc.)

Three consecutive values above 64:

=OR( A1 + B1 + C1 > 192 , B1 + C1 + D1 > 192 , etc.)

Upvotes: 1

Related Questions