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

dpaton05

Well-known Member
Joined
Aug 14, 2018
Messages
2,343
Office Version
  1. 365
  2. 2016
Platform
  1. Windows
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.

VBA Code:
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
 

Excel Facts

Workdays for a market open Mon, Wed, Friday?
Yes! Use "0101011" for the weekend argument in NETWORKDAYS.INTL or WORKDAY.INTL. The 7 digits start on Monday. 1 means it is a weekend.

Zot

Well-known Member
Joined
Nov 26, 2020
Messages
1,410
Office Version
  1. 2016
Platform
  1. Windows
Will this works?
VBA Code:
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

Well-known Member
Joined
Aug 14, 2018
Messages
2,343
Office Version
  1. 365
  2. 2016
Platform
  1. Windows
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

Well-known Member
Joined
Nov 26, 2020
Messages
1,410
Office Version
  1. 2016
Platform
  1. Windows
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
VBA Code:
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

Well-known Member
Joined
Aug 14, 2018
Messages
2,343
Office Version
  1. 365
  2. 2016
Platform
  1. Windows

ADVERTISEMENT

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

dpaton05

Well-known Member
Joined
Aug 14, 2018
Messages
2,343
Office Version
  1. 365
  2. 2016
Platform
  1. Windows
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

Well-known Member
Joined
Nov 26, 2020
Messages
1,410
Office Version
  1. 2016
Platform
  1. Windows

ADVERTISEMENT

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
VBA Code:
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.
VBA Code:
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
VBA Code:
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

Well-known Member
Joined
Aug 14, 2018
Messages
2,343
Office Version
  1. 365
  2. 2016
Platform
  1. Windows
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

Active Member
Joined
Apr 29, 2016
Messages
347
Try this
VBA Code:
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

Well-known Member
Joined
Aug 14, 2018
Messages
2,343
Office Version
  1. 365
  2. 2016
Platform
  1. Windows
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?
 

Forum statistics

Threads
1,136,321
Messages
5,675,064
Members
419,548
Latest member
wfarzand

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top