VBA If Functions, blank cells, and message box

[Chris1025]

I’m looking to add a message box to an existing if function. If i search something in a range, i have it highlight that result, so long as it returns it. If it doesn’t i would like to add a message box that pops up, like an error. Is this possible? The table range could possibly have some blank cells that i do not want to remove also, so i would like to ignore those if possible.

 

[Chris1025]

Hi,
try this update to your code & see if does what you want

Sub Scan_Barcode()
  
    Dim BarCode     As Variant
    Dim rngScan     As Range, FoundBar   As Range
    Dim BarCount    As Long
  
    Set rngScan = Range("B1:I4000")
  
    Do
        BarCode = InputBox("Scan Barcode", "Scan")
      
        If Len(BarCode) > 0 Then
          
            If IsNumeric(BarCode) Then BarCode = Val(BarCode)
          
            BarCount = Application.CountIf(rngScan, BarCode)
          
            If BarCount = 1 Then
              
                Set FoundBar = rngScan.Find(BarCode, LookIn:=xlValues, lookat:=xlWhole, MatchCase:=False)
                If Not FoundBar Is Nothing Then FoundBar.Interior.Color = rgbYellow
              
            ElseIf BarCount > 1 Then
          
                MsgBox "Barcode: " & BarCode & Chr(10) & "There Are " & BarCount & " Duplicates Of This Barcode", 48, "Duplicates"
              
            Else
          
                MsgBox "Barcode: " & BarCode & Chr(10) & "No matching barcode found!", 64, "No Matches"
              
            End If
        End If
      
    ''cancel pressed
    Loop Until StrPtr(BarCode) = 0
  
End Sub

Solution is untested but do Note that I have replaced your For Next loop with the Range.Find method which should be much faster searching the specified range HOWEVER - this method can sometimes prove a little troublesome when searching some data types.

Dave

Dave-this worked exactly as needed.

Huge thanks to you and everyone else who helped!

 

[dmt32]

Dave-this worked exactly as needed.

Huge thanks to you and everyone else who helped!

most welcome glad we all able to assist you & appreciate your feedback

Dave

 

[Chris1025]

Hi,
try this update to your code & see if does what you want

Sub Scan_Barcode()
  
    Dim BarCode     As Variant
    Dim rngScan     As Range, FoundBar   As Range
    Dim BarCount    As Long
  
    Set rngScan = Range("B1:I4000")
  
    Do
        BarCode = InputBox("Scan Barcode", "Scan")
      
        If Len(BarCode) > 0 Then
          
            If IsNumeric(BarCode) Then BarCode = Val(BarCode)
          
            BarCount = Application.CountIf(rngScan, BarCode)
          
            If BarCount = 1 Then
              
                Set FoundBar = rngScan.Find(BarCode, LookIn:=xlValues, lookat:=xlWhole, MatchCase:=False)
                If Not FoundBar Is Nothing Then FoundBar.Interior.Color = rgbYellow
              
            ElseIf BarCount > 1 Then
          
                MsgBox "Barcode: " & BarCode & Chr(10) & "There Are " & BarCount & " Duplicates Of This Barcode", 48, "Duplicates"
              
            Else
          
                MsgBox "Barcode: " & BarCode & Chr(10) & "No matching barcode found!", 64, "No Matches"
              
            End If
        End If
      
    ''cancel pressed
    Loop Until StrPtr(BarCode) = 0
  
End Sub

Solution is untested but do Note that I have replaced your For Next loop with the Range.Find method which should be much faster searching the specified range HOWEVER - this method can sometimes prove a little troublesome when searching some data types.

Dave

Hey Dave,

Would it be possible to add a command to jump to the found cell using the above? Only for the instance when it’s not a duplicate?

 

[dmt32]

Would it be possible to add a command to jump to the found cell using the above? Only for the instance when it’s not a duplicate?

Try adding additional code shown in BOLD & see if does what you want

Set FoundBar = rngScan.Find(BarCode, LookIn:=xlValues, lookat:=xlWhole, MatchCase:=False)
If Not FoundBar Is Nothing Then FoundBar.Interior.Color = rgbYellow: FoundBar.Select

Dave

 

[Chris1025]

Yes that worked! Thanks, Dave!

 

[Chris1025]

My data source periodically will have preceding zeros, to which i do NOT want ignored. However the code drops the zeros, even when i have zeros in the input box. Any way to avoid that?

 

[Chris1025]

See the image. This is an example of the above. I changed the code to…

If IsNumeric(BarCode) Then BarCode = (BarCode)

From…

If IsNumeric(BarCode) Then BarCode = Val(BarCode)

And it seems to return results as needed. However it’s now recognizing the two below as duplicates. They have the same first 16 digits, but last four are different so they’re unique.

 

[Chris1025]

Try adding additional code shown in BOLD & see if does what you want

Set FoundBar = rngScan.Find(BarCode, LookIn:=xlValues, lookat:=xlWhole, MatchCase:=False)
If Not FoundBar Is Nothing Then FoundBar.Interior.Color = rgbYellow: FoundBar.Select

Dave

hey bud, this worked. would you have any idea on the other minor issue i'm having? Also should i be posting these in different threads? since this post is marked solved?