[SOLVED] Finding a match after Barcode scanned

[creekybones]

Hello All,
A while ago I had some help from this forum to develop an inventory database, which is working well. Unfortunately I need your help again. I have searched and found a few related posts that I though that I could use to help me, but I am getting nowhere.

So hopefully you wont mind in helping again.

I now need to add a VBA to monitor Cell E3 on Sheet 'ALL STOCK NEW. When I scan a barcode into this active cell E3, I would like a VBA to search for a match and select that cell. After this I can add my existing VBA to select the row and copy to another sheet.

I have attached the Workbook.

Hopefully you don't mind help and hope you understand my request or may be point me in the right direction.

Thanks in advance.

Creeky

[MatrixMan]

You should be able to use the Worksheet Change event to monitor cell E3.

Where are you searching for matching barcodes? I don't see that column heading in the sheet.

[creekybones]

Hello MatrixMan
Thank you very much for your reply, I’ll have a look at the change event.
I will be looking for a match in the columns labelled ‘Part Number’ and ‘RS No’. Then select the matching cell.

[MatrixMan]

Great - it should be straight-forward, provided the change event recognises the cell change when you scan a barcode; assuming it does, then in the change event you can use .Find to look for an exact match (LookAt:=xlWhole) in the part number column.

Let me know if you get stuck - good luck.

[creekybones]

Perfect. I’ll have a go and get back to you.
Thanks for your help.

[creekybones]

Hello Matrix man,
I have managed to get the code working when the cell value changes, but it only works with numbers (i.e 12). If I enter a number with hyphen in it (ie 877-1160) the vba code does not work at all, nothing happens. I don't even get the Not Found message box. This also happens if I enter 'M4x10' into the cell.

Would be great-full if you could help.

Private Sub Worksheet_Change(ByVal Target As Range)
Dim iVal As Long
Dim cell As Range

If Target.Address = "$E$3" Then
If VarType(Target.Value) = vbDouble Then
iVal = Target.Value
Set cell = Range("B4", Cells(Rows.Count, "B").End(xlUp)).Find(What:=iVal, LookIn:=xlFormulas, lookat:=xlWhole)
If cell Is Nothing Then
MsgBox "Not found: " & iVal
Else
cell.Select
End If
End If
End If
End Sub

[MatrixMan]

Can you post your workbook with the current code?

[MatrixMan]

Actually ...

You have defined iVal as long but then assigned Target.Value to it if it is double ... if you have a hyphen or other text characters it won't be a double and will skip your code.

[MatrixMan]

So something like this:

Please Login or Register  to view this content.

[creekybones]

Hello Matrix Man,
Your code works, Thank you very much for your help.

Very much appreciated.

I can now finish the rest of the coding.

Thank you for spending the time to help.

[MatrixMan]

You're welcome ... don't forget to mark the thread solved