Inventory using Excel

[Naruteaux10]

This is in regards to this thread: http://www.excelforum.com/excel-gene...ing-excel.html

The code provided in this thread worked really well. But, I am trying to figure out a way so that when a UPC code is scanned, thus changes the quantity for an item, the cursor moves to that specific cell where the quantity changes. So basically, I just need a Add&Find active cell combined. Is this possible? Any advice would be appreciated.

Thank you!

[dflak]

It took a bit of experimentation but here is what worked for me. I assumed that the cell that got changed was in the range "C1:C20". I simply put a change event on the sheet. Cells changed by formula will not trigger an event. But cells written to by VB code can trigger an event.

Please Login or Register  to view this content.

Target is the cell that changed.

I used the following code to trigger it.

Please Login or Register  to view this content.

[Naruteaux10]

Thanks for looking into this! One more questions:

Is there a way to have the cursor move to the changed cell ONLY when a new code is scanned, and therefore adding it to the bottom of the list. Here is what the code looks like now for ADDING to the inventory:


Private Sub Worksheet_Change(ByVal Target As Range)
Select Case Target.Address
Case "$A$4" 'add
If Target.Value <> "" Then
On Error Resume Next
Set xitem = Range("C8", Range("C" & Rows.Count) _
.End(xlUp)).Find(Range("A4").Value)
With xitem.Offset(0, -1)
.Value = .Value + 1
End With
On Error GoTo 0
If xitem Is Nothing Then
With Range("C" & Rows.Count).End(xlUp).Offset(1, 0)
.Value = Target.Value
.Offset(0, -1) = 1
End With
End If
Range("A4") = ""
Range("A4").Select
End If


Thanks Again!

[dflak]

It might go a bit easier if you also included a sample workbook. It is tough to see what you are doing from the code alone. I am thinking that we would have to search the existing columns to see if the value already exists and if it does add one to it, otherwise create a new record at the end. Is this what you are looking for?

[Naruteaux10]

Got it, see attached.

[dflak]

I think this will do what you want to do. The only thing I don't know how to address is when a new UPC is added, how do you get the Item Description? Is that done manually or by some other process?

[Naruteaux10]

Great, thanks! Yes this seems to work. I also found a relatively simple way to do this (see attached if anybody is interested). This moves the cursor to the position of the scanned UPC after adding, subtracting, or finding. But yeah, the only thing I can't figure out is how to get the cursor to move to the position of the added UPC when a new UPC is scanned/typed. If anybody can figure this out, please let me know!

Thanks again