Search Range for Value Greater than 2 | VBA

[Frankinstyyn]

Hi all,

I'm after some code which will check a range and run a Sub if any where in the cell it show 2 DAY(S) or above, if not run a different sub.

The overall idea is to have code that checks between 2 parameters, if '2 DAY(S)' or above appears, show the rest of the information in that cell.

For example, cell A14 says "2233 FORUM5 INBOUND SALES NOT RETRIEVED FOR 2 DAY(S)" I would like the code to check the whole cell and identify that it says '2 DAY(S)' which will then run a sub.

If it checks the cells and there is no '2 DAY(S)', then move onto the next cell within the range. If it cannot find any '2 DAY(S)' or above then run a specific sub.

I hope that makes sense!

Thanks in advance.

 

[Joe4]

Welcome to the Board

Here is some code that will loop through a range and tell you if it finds "DAY(S)" in each cell, and if it does, it will return the number in front of it. Otherwise, it will tell you if it cannot find "DAY(S)".
You should be able to incorporate this into what you want to do.

Sub MyCheck()

    Dim rng As Range
    Dim cell As Range
    Dim loc
    Dim num
    
    Set rng = Range("A13:A15")
    
    For Each cell In rng
        On Error GoTo err_chk
        loc = InStr(cell.Value, "DAY(S)")
        On Error GoTo 0
        
        If loc > 0 Then
            num = Trim(Mid(cell.Value, loc - 3, 2))
            MsgBox num & " found in cell " & cell.Address(0, 0)
        Else
            MsgBox "No value found in cell " & cell.Address(0, 0)
        End If
    Next cell
    
    Exit Sub
    

err_chk:
    loc = 0
    Err.Clear
    Resume Next
    
End Sub

 

[Frankinstyyn]

Hi Joe,

Thank you very much from coming back to me on this.

After running the code, it certainly pulls back some information which I can definitely use so thank you massively.

Is there a way to have the code pull greater than 1 ">1" rather than "DAY(S)"? And also instead of showing a msg box, run sub "Order"?

Thank you again!!

I have amended your code to work with mine :D

Sub MyCheck()

    Dim rng As Range
    Dim cell As Range
    Dim loc
    Dim num
    
    Set rng = Range("H:H")
    
    For Each cell In rng
        On Error GoTo err_chk
        loc = InStr(cell.Value, ">1")
        On Error GoTo 0
        
        If loc > 0 Then
            num = Trim(Mid(cell.Value, loc - 3, 2))
            MsgBox num & " found in cell " & cell.Address(0, 0)
        Else
            MsgBox "No value found in cell " & cell.Address(0, 0)
        End If
    Next cell
    
    Exit Sub
    


err_chk:
    loc = 0
    Err.Clear
    Resume Next
    
End Sub

 

[Joe4]

After running the code, it certainly pulls back some information which I can definitely use so thank you massively.

What exactly does your data look like (if not like what you originally posted)?
Can you post a few more representative samples of your data, and what you want to happen in each case?

If your data really looks like what you originally posted, then the "num" variables returns the exact number before "DAY(S)".
So then you would just need to check to see if that number is greater than 1, i.e.

        If loc > 0 Then
            num = Trim(Mid(cell.Value, loc - 3, 2))
            If num > 1 Then
                [COLOR=#ff0000]Call Order[/COLOR]
            End If

And also instead of showing a msg box, run sub "Order"?

Of course. You can make it do whatever you want. I showed you how to call another macro above (line in red).

 

[Frankinstyyn]

Yes! Your code makes sense, thank you...

Just to give some context, my idea is to take the txt file, copy into excel, split the data into columns and check columns 'G:H'

If in column G or H a single number is 2 or above, then call 'Order' which sends email A.

If there are no numbers 2 or above in column G or H, then send email B.

I have added in your code from above and now, I'm seeing an error: "Compile Error: Block If without End If"

I have changed the font to red to show what I think is different, I've also added my thanks again for the help

Sub MyCheck()

    Dim rng As Range
    Dim cell As Range
    Dim loc
    Dim num
    
    Set rng = Range("[COLOR=#ff0000]H:H[/COLOR]")
    
    For Each cell In rng
        On Error GoTo err_chk
        loc = InStr(cell.Value, "[COLOR=#ff0000]>1[/COLOR]")
        On Error GoTo 0
        
        If loc > 0 Then
            num = Trim(Mid(cell.Value, loc - 3, 2))
            If num > 1 Then
                Call Order
            End If
    
    Exit Sub
    


err_chk:
    loc = 0
    Err.Clear
    Resume Next
    
End Sub

 

[Joe4]

I have added in your code from above and now, I'm seeing an error: "Compile Error: Block If without End If"

Sometimes, Excel's error debugging isn't too helpful. Other times, it tells you exactly what the issue. This falls into the later category - you are missing and "End If".
Every If ... Then block that goes more than one line needs an "End If" for every "If". You are missing an "End If". Look at my original code - two "If" statements, two "End If" statements.
Note in my second post, I was just showing you the beginning of your code, not the entire block of code.

I think I may see a few other issues with your code.

You say:

If there are no numbers 2 or above in column G or H, then send email B.

Yet, you are only looking in column H:

    Set rng = Range("[COLOR=#ff0000]H:H[/COLOR]")

Also, I don't think this does what you think it does:

        loc = InStr(cell.Value, "[COLOR=#ff0000]>1[/COLOR]")

The InStr function looks for the EXACT text string. So, that in line is NOT checking for a number greater than 1, it is looking in the string to see if it can find the exact phrase ">1". So anything other than ">1" will return email B.

If you look at my original code, I am using InStr to locate exactly where the "DAY(S)" appears in your string, then I am extracting the number in front of that word, then doing the math comparison on that number. I believe that is how you want to do it (unless your data really does not look like what you first posted).

 

[Frankinstyyn]

Hi Joe,

Thank you for the detailed and lengthy reply, I appreciate your patience and teaching me more!

From what I understand from your reply, I have amended the code to the below...

Sub MyCheck()

    Dim rng As Range
    Dim cell As Range
    Dim loc
    Dim num
    
    Set rng = Range("G:H")
    
    For Each cell In rng
        On Error GoTo err_chk
        loc = InStr(cell.Value, "DAY(S)")
        On Error GoTo 0
        
        If loc > 0 Then
            num = Trim(Mid(cell.Value, loc - 3, 2))
            If num < 1 Then
                Call SendMailU2
            If num > 1 Then
                Call SendMailO2
            End If
        End If
    
    Exit Sub


err_chk:
    loc = 0
    Err.Clear
    Next
    
End Sub

On the back of this, I was seeing a compile error: For without Next. According to your explanation, there needs to be an opening and closing, which I amended and removed the resume before the last next and the error cleared...

This is my full data breakdown:

PS633/1                        HEADS UP                                                         04:39:12    11.02.19                               ---------------------------------------
FAVOURITE STORES


-----STORE-----                INBOUND/OUTBOUND                          FAILURE MESSAGE
NUMBER  TOWN


7145    FORUM1                      INBOUND                  SALES NOT RETRIEVED FOR                            1 DAY(S)
                                                             STOCK MOVEMENTS NOT RETRIEVED FOR                  1 DAY(S)


**************************************************  END OF PREMIER DETAIL REPORT   *************************************************


PS633/2                        HEADS UP                                                         04:39:12    11.02.19
                               ---------------------------------------
NON FAVOURITE STORES


-----STORE-----                INBOUND/OUTBOUND                          FAILURE MESSAGE
NUMBER  TOWN


1234    FORUM2                     OUTBOUND                  LATEST WAREHOUSE DELIVERIES FAILED


1111    FORUM3                     OUTBOUND                  LATEST WAREHOUSE DELIVERIES FAILED


2222    FOrUM4                     OUTBOUND                  LATEST WAREHOUSE DELIVERIES FAILED


2233    FORUM5                     INBOUND                   SALES NOT RETRIEVED FOR                            2 DAY(S)
                                                             STOCK MOVEMENTS NOT RETRIEVED FOR                  2 DAY(S)
                                   OUTBOUND                  LATEST WAREHOUSE DELIVERIES FAILED


5566    FORUM6                     INBOUND                   SALES NOT RETRIEVED FOR                            1 DAY(S)
                                                             STOCK MOVEMENTS NOT RETRIEVED FOR                  1 DAY(S)


0099    FORUM7                     OUTBOUND                  LATEST WAREHOUSE DELIVERIES FAILED
**************************************************END OF NON PREMIER DETAIL REPORT *************************************************

For context, I would like to identify any 4 digit number in column A that has 2 or more DAY(S) failure (columns G & H) to be added into an email (EMAIL A) to eventually look like this:

Good Morning, 

There is 1 store on the report this morning.

1234 FORUM5 – Sales and stock failure, team investigating.

If there are no numbers above 1, then send the below email, EMAIL B

Good Morning, 

There are no stores on the report this morning.

 

[Joe4]

I see if your code that you are checking for > 1 and < 1, but never = 1. So no email would be generated if it is exactly equal to one.
You may want to change:

            If num < 1 Then

to

            If num <= 1 Then

or something to that affect to account for that.

I would like to identify any 4 digit number in column A that has 2 or more DAY(S) failure (columns G & H) to be added into an email (EMAIL A) to eventually look like this

You can check for multiple conditions.

First, I would probably change how you are looping. Find the last row with data, then loop through all the rows.
Then pull the important values from specific columns into variables (not necessarily, but makes it easier to work with and the code easier to read).

I re-wrote my original code. I have the part that pulls the DAY number from columns G/H moved out of the Sub and into its own Function, so it can easily be called and re-used. I changed the loop code, like I mentioned above. And I created another Sub called "EmailA", which really just returns a MsgBox (instead of sending an email), just so we can get instant feedback. I showed how you can pass values to that "EmailA" sub that you can use in writing the output.

So it may not do exactly what you want, but it should have the logic and pieces for you to do most (if not all) of what you want to do (provided you haven't left off any other important details!).

Sub MyCheck()

    Dim lr As Long
    Dim r As Long
    Dim num As Long
    Dim colA, colG, ColH
    Dim colB As String
    Dim daysG As Long
    Dim daysH As Long

'   Use column A to find last row with data
    lr = Cells(Rows.Count, "A").End(xlUp).Row

'   Loop through all rows starting on row 2
    For r = 2 To lr
'       Get values from columns A, B, G, and H
        colA = Cells(r, "A").Value
        colB = Cells(r, "B").Value
        colG = Cells(r, "G").Value
        ColH = Cells(r, "H").Value
'       Get days from from entries in column G and column H
        daysG = GetNumber(colG)
        daysH = GetNumber(colG)
'       Call EmailA if column A is 4 characters long, and number in G or H >= 2
        If (Len(colA) = 4) And ((colG >= 2) Or (ColH >= 2)) Then
            Call EmailA(colB, r)
        End If
    Next r

End Sub


Sub EmailA(town As String, rownum As Long)
'   Parameters:
'       town - value from column B
'       rownum - row number of record

    MsgBox "Town: " & town & " from row number: " & rownum & " meets the requirments."
    
End Sub


Function GetNumber(myEntry) As Long
'   Get number just before "DAY(S)"
'   Will return 0 if it cannot find it

    Dim loc
    
    loc = InStr(myEntry, "DAY(S)")
    
    If loc > 0 Then
        GetNumber = Trim(Mid(myEntry, loc - 3, 2)) + 0
    Else
        GetNumber = 0
    End If
    
End Function