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]

Sub Scan_Barcode

box=Inputbox(”Scan barcode”)

Dim R As Range
Set R = Range (“B1:I4000”)

For Each Cell in R

If (Cell.value=box) and (cell.value <> “”) then
cell.interior.colorindex=6

End if
Next

End Sub

 

[Chris1025]

I also did have a message box in there and it would pop up, but wouldn’t stop popping up I’m assuming due to returning all of the negative results. I had it in with an else line. I’m super new to vba. Any help would be greatly appreciated.

 

[Joe4]

Welcome to the Board!

Try something like this:

Sub Scan_Barcode()

Dim box
Dim R As Range
Dim cell As Range
Dim num As Long

box = InputBox("Scan barcode")

Set R = Range("B1:I4000")

Application.ScreenUpdating = False

For Each cell In R
    If (cell.Value = box) And (cell.Value <> "") Then
        cell.Interior.ColorIndex = 6
        num = num + 1
    End If
Next

Application.ScreenUpdating = True

If num = 0 Then MsgBox "No matching values found!"

End Sub

 

[Alex Blakenburg]

The only think I added to what Joe had is the 2nd line below:
So that if you leave the box blank or hit cancel or hit the "X" it doesn't do the loop.
PS: If you only expect to find the barcode once you can also add a line to exit the loop when you find it.

    box = InputBox("Scan barcode")
    If box = "" Then Exit Sub

 

[Chris1025]

Welcome to the Board!

Try something like this:

Sub Scan_Barcode()

Dim box
Dim R As Range
Dim cell As Range
Dim num As Long

box = InputBox("Scan barcode")

Set R = Range("B1:I4000")

Application.ScreenUpdating = False

For Each cell In R
    If (cell.Value = box) And (cell.Value <> "") Then
        cell.Interior.ColorIndex = 6
        num = num + 1
    End If
Next

Application.ScreenUpdating = True

If num = 0 Then MsgBox "No matching values found!"

End Sub

Hey, joe. Thanks for having me, and the quick response. I tried this just as you wrote it out and It’s working great!

Id be curious to know if there’s a way to keep the input box up once started? And only have a message appear when it errors? In other words, just keep highlighting as i enter the searches?

Thanks again man!

 

[Chris1025]

The only think I added to what Joe had is the 2nd line below:
So that if you leave the box blank or hit cancel or hit the "X" it doesn't do the loop.
PS: If you only expect to find the barcode once you can also add a line to exit the loop when you find it.

    box = InputBox("Scan barcode")
    If box = "" Then Exit Sub

Appreciate that. In a perfect environment i wouldn’t have multiple responses on the same barcode, but would want it to possibly error out in the event there are multiple, is that possible?

 

[Alex Blakenburg]

Appreciate that. In a perfect environment i wouldn’t have multiple responses on the same barcode, but would want it to possibly error out in the event there are multiple, is that possible?

You just need to be more specific on what you want it to do in that scenario. Highlight the first one and error out on the second. Highlight the first one and provide a messagebox with the addresses or the others. Remove the highlight from the first one and have the message box tell you what the addresses are or something else.

if there’s a way to keep the input box up once started

See if this works, it just keeps calling the input box up again until you leave it empty, cancel or hit "X"
With only one match at a time I think you will want to see the screen update so I commented out the Screenupdating off/on lines

Sub Scan_Barcode()

    Dim box
    Dim R As Range
    Dim cell As Range
    Dim num As Long
    
    box = InputBox("Scan barcode")
    
    Set R = Range("B1:I4000")
    
    'Application.ScreenUpdating = False
    
    Do Until box = ""
        For Each cell In R
            If (cell.Value = box) And (cell.Value <> "") Then
                cell.Interior.ColorIndex = 6
                num = num + 1
            End If
        Next
        box = InputBox("Scan barcode")
    Loop
    
    'Application.ScreenUpdating = True
    
    If num = 0 Then MsgBox "No matching values found!"

End Sub

 

[Chris1025]

You just need to be more specific on what you want it to do in that scenario. Highlight the first one and error out on the second. Highlight the first one and provide a messagebox with the addresses or the others. Remove the highlight from the first one and have the message box tell you what the addresses are or something else.


See if this works, it just keeps calling the input box up again until you leave it empty, cancel or hit "X"
With only one match at a time I think you will want to see the screen update so I commented out the Screenupdating off/on lines

Sub Scan_Barcode()

    Dim box
    Dim R As Range
    Dim cell As Range
    Dim num As Long
   
    box = InputBox("Scan barcode")
   
    Set R = Range("B1:I4000")
   
    'Application.ScreenUpdating = False
   
    Do Until box = ""
        For Each cell In R
            If (cell.Value = box) And (cell.Value <> "") Then
                cell.Interior.ColorIndex = 6
                num = num + 1
            End If
        Next
        box = InputBox("Scan barcode")
    Loop
   
    'Application.ScreenUpdating = True
   
    If num = 0 Then MsgBox "No matching values found!"

End Sub

You guys are awesome!!

This does keep the scan barcode box up, until i cancel or submit a blank. Then it returns the error. However when i scan something that is not in the range, it just brings the box back up, and doesn’t error out.

And to answer your earlier question - not sure how complex it would be, if it scanned something that happened to be in the range twice, i would want it to error out with “duplicate barcodes found” or something of the sort.

Not sure if that’s possible or to complex, at the very least I’d like it to error in some capacity, but not highlight in the event there are duplicates. Is that possible?

 

[dmt32]

not sure how complex it would be, if it scanned something that happened to be in the range twice, i would want it to error out with “duplicate barcodes found” or something of the sort.

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