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]

Will this works?

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, rngFound 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))
        Set rngFound = ReqRng.Find(Req)
        If Not rngFound Is Nothing Then
            rngFound.Offset(, -1) = PO
        Else
            MsgBox "No PO Found"
        End If
    Next Sh
End Sub

 

[dpaton05]

Thanks for the reply Zot. That doesn't seem to work. You have removed some of my code and it was working perfectly so I thought I had to just add some code to find out if there was a request number. Anyway, you may have improved on my code. I am not that good at reading vba yet so I am not sure.



What happens when I run the procedure is I get the message box saying "No PO found", and I hit ok and it reappears 11 times. Not sure if it is looking for the PO number or Req as I need it to be looking to see if the request number exists (Req). In my test spreadsheet, there is 3 instances of the request number I am trying to check if it exists. The request number is in column C in each monthly sheet, starting from row 4 down.

 

[Zot]

Thanks for the reply Zot. That doesn't seem to work. You have removed some of my code and it was working perfectly so I thought I had to just add some code to find out if there was a request number. Anyway, you may have improved on my code. I am not that good at reading vba yet so I am not sure.



What happens when I run the procedure is I get the message box saying "No PO found", and I hit ok and it reappears 11 times. Not sure if it is looking for the PO number or Req as I need it to be looking to see if the request number exists (Req). In my test spreadsheet, there is 3 instances of the request number I am trying to check if it exists. The request number is in column C in each monthly sheet, starting from row 4 down.

My fault. I forgot that about the loop. Not testing it since no example. The problem with For Each is that there is no index to mark the end of loop. Using For n = 1 to x can know
If PO is found, no need to keep looping. Just exit. I have to use Goto to skip before end of Each Loop

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
                GoTo Found
        Next Cel
        MsgBox "No PO Found"
Found:
    Next Sh
End Sub

 

[dpaton05]

The sub stops with an error and in the 5th last line, highlights the next, next to Cel and says next without for.

 

[dpaton05]

Found the issue with that. It was missing an end if. I put that in and it went back to displaying the same message box as described in post 3.

 

[Zot]

Found the issue with that. It was missing an end if. I put that in and it went back to displaying the same message box as described in post 3.

Forgot End If. My bad. Where you put the End IF. It should be

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
                GoTo Found
            End If
        Next Cel
        MsgBox "No PO Found"
Found:
    Next Sh
End Sub

This will keep looping until match is found and GoTo Found, skipping the No PO Found msg. If nothing is found until end of For Each loop, then you will see No PO Found for that particular month.

For Each Cel In ReqRng
     If Val(Cel) = Val(Req) Then
          Cel.Offset(, -1) = PO
          GoTo Found
     End If
Next Cel

If you want to search all the months and only then to display msg, then

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
                GoTo Found
            End If
        Next Cel
    Next Sh
    MsgBox "No PO Found"
Found:
End Sub

 

[dpaton05]

Thanks for that Zot, I want to look for it in the all the months so your last section of code worked. Now, I have 3 different procedures that I want to check to see if the request number exists. How could I separate the part that copies the purchase order number from the part that checks if the request number exists in the UpdateEverySheet sub.

I want to be able to call the sub to see if the request number exists.

 

[Phuoc]

Try this

Sub UpdateEverySheet() '(Req As Range, PO As Range)
    Dim lcount As Long
    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
                lcount = lount + 1
            End If
        Next Cel
    Next Sh
    If lcount = 0 Then
        MsgBox "No PO Found"
    Else
        MsgBox lcount & " FO is found"
    End If
End Sub

 

[dpaton05]

I was thinking of breaking up the UpdateEverySheet sub into 2 separate subs. One sub that copies the purchase order number to the sheets and one sub that checks if the request number exists. I looked at the UpdateEverySheet sub but I couldn't work out how to separate them.

Could either of you help me with that please?