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