Finding a number in a column and if it doesn't exist, let the user know

[dpaton05]

I have a sub to enter a purchase order number against every instance of a request number. The procedure works perfectly, but it doesn't let you know if you have entered the wrong request number. This will be known if the request number does not exist on the spreadsheet. What code do I need to add to check if the request number exists in the spreadsheet and let the user know if it doesn't?

This is the sub that works but without letting the user know if the request number doesn't exist.

Sub UpdateEverySheet() '(Req As Range, PO As Range)
    Dim Sh, ws As Worksheet, Cel As Range, ReqRng As Range
    Dim Req As Range, PO As Range
        Set Req = Cells(18, 2)
        Set PO = Cells(20, 2)
    If UCase(PO) = "X" Then PO = ""
    For Each Sh In Array("January", "February", "March", "April", "May", "June", _
            "July", "August", "September", "October", "November", "December", "Cancellations")
        Set ws = Sheets(Sh)
        Set ReqRng = ws.Range("C4", ws.Range("C" & Rows.Count).End(xlUp))
        For Each Cel In ReqRng
            If Val(Cel) = Val(Req) Then Cel.Offset(, -1) = PO
        Next Cel
    Next Sh
End Sub

 

[Zot]

Again. Not tested. Make the sub serves both function.

Sub UpdateEverySheet() '(Req As Range, PO As Range)
    Dim Sh, ws As Worksheet, Cel As Range, ReqRng As Range
    Dim Req As Range, PO As Range
    Dim Resp As Integer
        Set Req = Cells(18, 2)
        Set PO = Cells(20, 2)
    If UCase(PO) = "X" Then PO = ""
    MsgBox "Do you want to copy PO number?", vbYesNo + vbQuestion, "PO Search"
    For Each Sh In Array("January", "February", "March", "April", "May", "June", _
            "July", "August", "September", "October", "November", "December", "Cancellations")
        Set ws = Sheets(Sh)
        Set ReqRng = ws.Range("C4", ws.Range("C" & Rows.Count).End(xlUp))
        For Each Cel In ReqRng
            Select Case Resp
                Case vbYes
                    If Val(Cel) = Val(Req) Then
                        Cel.Offset(, -1) = PO
                        GoTo Found
                    End If
                Case Else
                    MsgBox "PO found in " & Sh & " row " & Cel.Row
                    End
            End Select
        Next Cel
    Next Sh
    MsgBox "No PO Found"
Found:
End Sub

 

[Zot]

Looking at the code from high elevation, I asked myself why need to have that Found: index.

I can just need to replace GoTo Found with End statement since I need not do anything after that ?

 

[dpaton05]

Thanks for the code Phouc, I just realised that it does what I need it to do. Thanks for your help too Zot.

 

[dpaton05]

Actually, it doesn't do what I need but I managed to work it out.

I have a sub to check if the request number exists, which I can call from the other subs that need to know that information. I also have my original sub.

Sub CheckForReqNo() '(Req As Range, PO As Range)
    Dim Sh, ws As Worksheet, Cel As Range, ReqRng As Range
    Dim Req As Range, PO As Range, Counter As Long
        Set Req = Cells(18, 2)
    For Each Sh In Array("January", "February", "March", "April", "May", "June", _
            "July", "August", "September", "October", "November", "December", "Cancellations")
        Set ws = Sheets(Sh)
        Set ReqRng = ws.Range("C4", ws.Range("C" & Rows.Count).End(xlUp))
        For Each Cel In ReqRng
            If Val(Cel) = Val(Req) Then
                GoTo Found
            End If
        Next Cel
    Next Sh
    If Counter = 0 Then MsgBox "That request number does not exist."
Found:
End Sub

Sub UpdateEverySheet() '(Req As Range, PO As Range)
    Dim Sh, ws As Worksheet, Cel As Range, ReqRng As Range
    Dim Req As Range, PO As Range
        Set Req = Cells(18, 2)
        Set PO = Cells(20, 2)
    If UCase(PO) = "X" Then PO = ""
    For Each Sh In Array("January", "February", "March", "April", "May", "June", _
            "July", "August", "September", "October", "November", "December", "Cancellations")
        Set ws = Sheets(Sh)
        Set ReqRng = ws.Range("C4", ws.Range("C" & Rows.Count).End(xlUp))
        For Each Cel In ReqRng
            If Val(Cel) = Val(Req) Then Cel.Offset(, -1) = PO
        Next Cel
    Next Sh
End Sub

Thanks for the help guys.

 

[Alex Blakenburg]

I have a sub to check if the request number exists, which I can call from the other subs that need to know that information. I also have my original sub.

[QUOTE]
Sub CheckForReqNo() '(Req As Range, PO As Range)
    Dim Sh, ws As Worksheet, Cel As Range, ReqRng As Range
    Dim Req As Range, PO As Range, Counter As Long
        Set Req = Cells(18, 2)
    For Each Sh In Array("January", "February", "March", "April", "May", "June", _
            "July", "August", "September", "October", "November", "December", "Cancellations")
        Set ws = Sheets(Sh)
        Set ReqRng = ws.Range("C4", ws.Range("C" & Rows.Count).End(xlUp))
        For Each Cel In ReqRng
            If Val(Cel) = Val(Req) Then
                GoTo Found
            End If
        Next Cel
    Next Sh
    If Counter = 0 Then MsgBox "That request number does not exist."
Found:
End Sub
[/QUOTE]

Zot suggested using find earlier in the piece and if you modify your code to use that it should be quite a bit faster.
Below is your code modified to use find.
If you want other programs to use this it should be converted to a function, with all the variables passed to it.
Probably call it somethings like ReqNoExists and return a True / False to the calling procedure.

In this form you are probably better off calling this first and having this call the main routine.

Sub CheckForReqNo() '(Req As Range, PO As Range)
    Dim Sh, ws As Worksheet, Cel As Range, ReqRng As Range
    Dim Req As Range, PO As Range, Counter As Long
    Dim ReqFindRng As Range             ' XXX Added This
    Dim ReqNoExists As Boolean          ' XXX Added This

    Set Req = ActiveSheet.Cells(18, 2)  ' XXX made it clear this needs to be the active sheet
    ReqNoExists = False

    For Each Sh In Array("January", "February", "March", "April", "May", "June", _
            "July", "August", "September", "October", "November", "December", "Cancellations")
   
        Set ws = Sheets(Sh)
        Set ReqRng = ws.Range("C4", ws.Range("C" & Rows.Count).End(xlUp))

        ' XXX Changed from here on
        Set ReqFindRng = ReqRng.Find(What:=Req.Value2, LookIn:=xlValues, lookat:=xlWhole)

        If Not ReqFindRng Is Nothing Then
            ReqNoExists = True
            Exit For
        End If
    Next Sh
    If ReqNoExists = False Then
        MsgBox "That request number does not exist."
    Else
        ' Do something - possibly call the main routine
    End If

End Sub

 

[dpaton05]

Thanks for that Alex. By saying "if you want other programs to use this", are you referring to other subs? I am still new to programming and have not worked much with functions. I would like to make this as fast as I can so I was wondering if you could help me a bit, please?

I don't understand the code you have written, could you comment each line to help me learn what it does please?

Thanks

 

[Zot]

Thanks for that Alex. By saying "if you want other programs to use this", are you referring to other subs? I am still new to programming and have not worked much with functions. I would like to make this as fast as I can so I was wondering if you could help me a bit, please?

I don't understand the code you have written, could you comment each line to help me learn what it does please?

Thanks

I believe that @Alex Blakenburg provides you an alternative subroutine to do the same. The Find function is faster than looping line by line and will be significant if you have many lines to search. My code has straightforward flow which is easy to understand.

 

[Alex Blakenburg]

Thanks for that Alex. By saying "if you want other programs to use this", are you referring to other subs? I am still new to programming and have not worked much with functions. I would like to make this as fast as I can so I was wondering if you could help me a bit, please?

I don't understand the code you have written, could you comment each line to help me learn what it does please?

Thanks

Sorry yes I was referring to other subs calling it and it was a response to your statement:-
"I have a sub to check if the request number exists, which I can call from the other subs that need to know that information."

The line
Set ReqFindRng = ReqRng.Find(What:=Req.Value2, LookIn:=xlValues, lookat:=xlWhole)
is the equivalent of doing find in Excel - see image below.

Within the same Excel session, Excel defaults these settings (see image below) to the last time you used them so it pays to set them explicitly when you use the function in VBA.

The find returns is a Cell Object which is a range object hence the Dim ReqFindRng as Range, needing the Set in the assignment line.
Also being a range object if it can't find it the returns a value of Nothing which is referenced as If ReqFindRng Is Nothing then.
However we want to test if it has been find so you put Not in front of it If Not ReqFindRng Is Nothing then

In this case if we find even 1 occurrence we can stop looking so we exit the loop with the Exit For
But before we exit the loop set ReqNoExists = True, so we then have a True / False value to use to decide what action we want the code to perform.

The key difference between a Sub and a Function is that a function returns a value.
So if this was a function, you could call it Function ReqNoExists(any parameters) as Boolean and in your main sub you could go straight to the If ReqNoExists(any parameters) = False Then
Probably too much to cover here. Common uses include functions for FileExists and WorksheetExists.

Advantage of Find:
Its really fast

Disadvantage:
If your source data is not reliable and you could get a Requisition as text with spaces eg "123456 ", find xlWhole won't find it while Val part in this statement If Val(Cel) = Val(Req) Then will convert both sides to a number and therefore will still "find" it.
If you requisitions are a guaranteed length you could switch to xlPart but if the aren't then you could look for "123456" and it would find "99123456" which you don't want.

On the flip side Val will only work with fully numeric requisition numbers, if you have a dash or alpha character in the req number then Val would not work.