# Microsoft Office Application Help - Excel Help forum > For Other Platforms(Mac, Google Docs, Mobile OS etc) >  >  Excel to beep if cell is empty.

## Nitefox

I am using a bar code scanner to scan data into cells $G6 and below. When the code is entered the cell next to it $F6 looks up a data list on another sheet to find that bar code with the name for that item listed in the column next to it and pastes it in that cell ($F6) with the following code:
=IFERROR(VLOOKUP($G6, 'Drop Down Data'!$B$2:$C$1048576, 2, FALSE),"")
Is there a way I can have it beep if it doesn't find the serial.

So basically if im say scanning in 100 items, as soon as it comes across a bar code it cant find the computer should beep to let me know the item name needs to be entered manually.

Thanks,
James

----------


## fredlo2010

Try something like this in the modeule for the sheet you are working on. It works in my windows computer  :Smilie: 




```
Please Login or Register  to view this content.
```


Thanks

----------


## Nitefox

Thank you for you response.
I think I must be doing something wrong, cus it doesn't seem to be working for me.
I entered the code into the the worksheet im working on, and changed the range to G10:G1048576 (this is the column that the serial number is scanned into).
I dont see in the code where it specifies the cell (one to the left in F column) which should triggers the beep when that cell doesnt find a data match from the serial number entered in G?

----------


## fredlo2010

Hi,

Your code should look like this




```
Please Login or Register  to view this content.
```


The word offset means get the value of the cell that's 1 column to my right. target.offset(manyrows,manycolumns)

and the code should be in the sheet ou are scanning to module. In the picture sheet1 module is highlighted.

Capture.PNG

----------


## Nitefox

I think were close, however it now beeps every time I enter data into a G cell (weather VLOOKUP in cell F finds data or not). I feel like it takes 1/2 a second for the data to come up, so it may just be that VB is checking before the F cell gets a chance to update if thats possible. If that is the case is it possible to just delay the VB slightly maybe?

----------


## fredlo2010

what kind of values does the Vlookup return?

Values, numbers, specific amount of digits? I am trying to find another way to identify the empty value.

Thanks

----------


## Nitefox

Its not really specific. Its looking up names for items of equipment. So basically on another page there are two really long rows of barcode with the name for that item in the column next to it. To save the user from having to manually enter the item when the barcode is scanned into the G cell, the F cell looks up that barcode on the on the other page to find the item name. This is the code it is using to find it:
=IFERROR(VLOOKUP($G6, 'Drop Down Data'!$B$2:$C$1048576, 2, FALSE),"")

So basically when the user is scanning say 100 items at a time they wont know until they get back to the computer if it didnt find an item in the list, and it would take them ages to locate which item that is (since the equipment is all different, and not scanned in any particular order). If the computer beeps to tell the user it cant find the item name then they can manually enter it there an then (since that item will still be in there hand).

----------


## fredlo2010

OK I figured out how to delay it by 2 seconds and passing and argument. A headache. I got it from here 

So now we need the even under the main sheet:




```
Please Login or Register  to view this content.
```


and in another module the beepsound code




```
Please Login or Register  to view this content.
```


With this we are delaying the action by 2 seconds. Try it and let me know if it worked  :Smilie:

----------


## Nitefox

Its working! When i first entered the code with the delay it didnt work. It then just dawned on me that it might be referencing the wrong cell, so I put data in the cell to the right instead and it didnt beep. So I went back to the original code (no delay) and changed the 1 to a -1 and now its working.
Thank you so much for your help!

----------


## Nitefox

Sorry, one last thing I just noticed. If I erase the numbers in the cell it also beeps. Is there something I can add to stop that (so it only check the next cell when adding data to the first one, not clearing it)?

----------


## fredlo2010

This should take care of it 




```
Please Login or Register  to view this content.
```


Thanks.

If you make any modification please post your final code here so we all now how the problem was fixed and others can learn.

Thanks

----------


## Nitefox

Works perfectly, thank you.

----------


## fredlo2010

I am glad I was able to help  :Smilie:

----------


## Nitefox

Sorry, I spoke a little too soon. I just discovered a small qwerk with it. Say if im deleting an entry, if I erase the Item name and serial number cells (G) at the same time then it brings up a Run-Time error 13. If I erase one cell first, then the second cell its ok. Is there a way to prevent the error when clearing both cells at the same time?

----------


## fredlo2010

whats the code being used and what line gets highlighted in yellow?

thanks

----------


## Nitefox

This is the code being used. I added the code to display the message box, and then bring up an input box to allow the user to enter the new record. Everything works fine for data entry when a record isnt located. It just brings up the error when I try to delete the record. 


Private Sub Worksheet_Change(ByVal Target As Range)

*If Intersect(Target, Range("G10:G1048576")) Is Nothing _
    Or Target.Cells.Count > 1 _
    Or Target.Value = vbNullString Then Exit Sub*

If Target.Offset(0, -1).Value = vbNullString Then
    Beep
    MsgBox ("Bar code not found, please enter item manually.")
    ActiveCell.Offset(-1, -1).Value = InputBox("New item name.", "Item Name.")

End If

----------


## fredlo2010

try this untested




```
Please Login or Register  to view this content.
```


please use code tags # when entering code, it makes it easier to read and copy

Thanks

----------


## Nitefox

That did it, lets me erase it without any issue now. Thanks for sticking with me.

----------


## fredlo2010

You are welcome  :Smilie:

----------

