Slight alteration to existing code

[ipbr21054]

Morning,
I am using the working code supplied below.
At present some part numbers had started with a zero but now some do & dont use the zero in the part number.
If i type say 12345 nothing is found BUT if i type 012345 i then see the required item.
To save me chaning a lot of part numbers can the code be altered & then check for either.

Private Sub CheckIfNumberExists_Click()
   Dim ans As String
   Dim Fnd As Range
   Do Until Not Fnd Is Nothing
      ans = InputBox("Enter search for value")
      If ans = "" Then Exit Sub
      Set Fnd = Range("A:A").Find(ans, , , xlWhole, , , False, , False)
      If Fnd Is Nothing Then
         If MsgBox("The Part Number " & ans & " Is Not In The List" & vbLf & vbLf & "Do You Want To Search Again ?", vbYesNo + vbCritical, "PART NUMEBR NOT FOUND") = vbNo Then Exit Sub
      Else
         If MsgBox("The Part Number  " & ans & "  Can Be Found In Row  " & Fnd.Row & vbLf & vbLf & "Search For Another ?", vbYesNo + vbExclamation, "PART NUMBER WAS FOUND") = vbYes Then Set Fnd = Nothing Else Exit Sub
      End If
   Loop
End Sub

 

[6StringJazzer]

I have not tested these solutions.

You can use xlPart instead of xlWhole. Then if you type 12345 it will match 012345. The problem is that it will also match 123456 and 912345.

You can use wildcards:

Set Fnd = Range("A:A").Find("*" & ans, , , xlWhole, , , False, , False)

Then if you type 12345 it will match 012345. The problem is that it will also match 912345.

You can check twice:

      Set Fnd = Range("A:A").Find(ans, , , xlWhole, , , False, , False)
      If Fnd Is Nothing Then
         Set Fnd = Range("A:A").Find("0" & ans, , , xlWhole, , , False, , False)
         If Fnd Is Nothing Then
            If MsgBox("The Part Number " & ans & " Is Not In The List" & vbLf & vbLf & "Do You Want To Search Again ?", vbYesNo + vbCritical, "PART NUMEBR NOT FOUND") = vbNo Then Exit Sub
         Else
            If MsgBox("The Part Number  " & ans & "  Can Be Found In Row  " & Fnd.Row & vbLf & vbLf & "Search For Another ?", vbYesNo + vbExclamation, "PART NUMBER WAS FOUND") = vbYes Then Set
         End If
      Else
         If MsgBox("The Part Number  " & ans & "  Can Be Found In Row  " & Fnd.Row & vbLf & vbLf & "Search For Another ?", vbYesNo + vbExclamation, "PART NUMBER WAS FOUND") = vbYes Then Set Fnd = Nothing Else Exit Sub
      End If

This adds more code but will find only 12345 or 012345.

 

[ipbr21054]

I did try part but with the current code in use it would only advise on the first item row number that was a match.
I dont need it to be more complicated.

 

[dmt32]

If your part numbers are 6 digit then you could force user to enter full number in your input box

Untested but maybe update to your code will do what you want

Private Sub CheckIfNumberExists_Click()
   Dim ans As String
   Dim Fnd As Range
   Dim msg As VbMsgBoxResult
   Do
    ans = InputBox("Enter Six Digit Part No.", "Search")
'cancel pressed
      If StrPtr(ans) = 0 Then Exit Sub
    If ans Like "######" Then
      Set Fnd = Range("A:A").Find(Val(ans), , , xlWhole, , , False, , False)
      If Fnd Is Nothing Then
         msg = MsgBox("The Part Number " & ans & " Is Not In The List" & vbLf & vbLf & _
                    "Do You Want To Search Again ?", vbYesNo + vbCritical, "PART NUMEBR NOT FOUND")
                    If msg = vbNo Then Exit Sub
      Else
         msg = MsgBox("The Part Number  " & ans & "  Can Be Found In Row  " & Fnd.Row & vbLf & vbLf & _
         "Search For Another ?", vbYesNo + vbExclamation, "PART NUMBER WAS FOUND")
         If msg = vbNo Then Exit Sub
      End If
    End If
    Set Fnd = Nothing
   Loop
End Sub

Dave

 

[ipbr21054]

Some are 5,6 & 7

 

[dmt32]

Some are 5,6 & 7

I don't have an immediate answer to that but see if the updated code works for 6 digit.

Dave

 

[ipbr21054]

No,
I enter 029022 but it advises me it isnt found.

Thanks for trying,ive an hour spare so im going to start and delete all zero's

 

[dmt32]

No,
I enter 029022 but it advises me it isnt found.

That may be due to my adding the Val function

Set Fnd = Range("A:A").Find(Val(ans), , , xlWhole, , , False, , False)

Could try removing it & see if helps

Set Fnd = Range("A:A").Find(ans, , , xlWhole, , , False, , False)

Dave