Barcode Scanner CheckIn/CheckOut - Stumped

[cval]

Hello everyone!

I am trying to build an excel macro that records a date and time a package is scanned. I also want to record the time the package leaves.

I am using a barcode scanner to record the package tracking number and I found this handy code that timestamps the package when its scanned (Check In time)

However, I cannot figure out how to have scan the same package number and record a Check Out time on the same row.

So Column "A" is Package # Column "B" Check In time Column "C" Check Out time.

Option Explicit
 
Private Sub Worksheet_Change(ByVal Target As Excel.Range)
'when entering data in a cell in Col A
 
On Error GoTo enditall
Application.EnableEvents = False
                    If Target.Cells.Column = 1 Then
                        With Target
                            If .Value <> "" Then
                            .Offset(0, 1).Value = Format(Now, "mm/dd/yyyy hh:mm:ss AM/PM")
                            End If
                        End With
                    End If
 
enditall:
Application.EnableEvents = True
End Sub

Any help would be greatly appreciated as I am just starting with VBA.

Thanks,
Christian

 

[Smitty]

I'd probably do something like this:

x = Range("A:A").Find(What:= Target.Value,After:=ActiveCell...

 

[cval]

I feel so close yet so far

Option Explicit

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
'when entering data in a cell in Col A

On Error GoTo enditall
Application.EnableEvents = False
Dim x As String
If x = Range("A:A").Find(What:=Target.Value, After:=ActiveCell, LookIn:=xlValues, LookAt:= _
                        xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False _
                        , SearchFormat:=False) Then
                        Target.Offset(0, 2).Value = Format(Now, "mm/dd/yyyy hh:mm:ss AM/PM")
                    Else
                    If Target.Cells.Column = 1 Then
                    With Target
                            If .Value <> "" Then
                            .Offset(0, 1).Value = Format(Now, "mm/dd/yyyy hh:mm:ss AM/PM")
                            End If
                    End With
                    End If
End If
    
enditall:
Application.EnableEvents = True
End Sub

 

[Smitty]

From a glance it looks pretty good. What's it doing/not doing?

 

[cval]

I'm glad I'm getting the synthax but It's only updating column b (even when the value is the same).

Pretty much its working as if the first If statement wasnt there.

 

[Smitty]

I don't have time right now, but I'll see if I can't test the code this evening.

 

[cval]

No worries - i have been at this for two days which is about as long as i have been using VBA for

I must admit as much as it is frustrating its really fun when it works!

 

[Smitty]

OK,

See if this does what you need:

<font face=Calibri><SPAN style="color:#00007F">Private</SPAN> <SPAN style="color:#00007F">Sub</SPAN> Worksheet_Change(<SPAN style="color:#00007F">ByVal</SPAN> Target <SPAN style="color:#00007F">As</SPAN> Range)<br>    <SPAN style="color:#007F00">'   Code goes in the Worksheet specific module</SPAN><br>    <SPAN style="color:#00007F">Dim</SPAN> rng <SPAN style="color:#00007F">As</SPAN> Range<br>    <SPAN style="color:#00007F">Dim</SPAN> strTracking <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">String</SPAN><br>        <SPAN style="color:#007F00">'   Set Target Range, i.e. Range("A1, B2, C3"), or Range("A1:B3")</SPAN><br>        <SPAN style="color:#00007F">Set</SPAN> rng = Target.Parent.Range("A:A")<br>        <SPAN style="color:#007F00">'   Only look at single cell changes</SPAN><br>            <SPAN style="color:#00007F">If</SPAN> Target.Count > 1 <SPAN style="color:#00007F">Then</SPAN> <SPAN style="color:#00007F">Exit</SPAN> <SPAN style="color:#00007F">Sub</SPAN><br>        <SPAN style="color:#007F00">'   Only look at that range</SPAN><br>            <SPAN style="color:#00007F">If</SPAN> Intersect(Target, rng) <SPAN style="color:#00007F">Is</SPAN> <SPAN style="color:#00007F">Nothing</SPAN> <SPAN style="color:#00007F">Then</SPAN> <SPAN style="color:#00007F">Exit</SPAN> <SPAN style="color:#00007F">Sub</SPAN><br>        <SPAN style="color:#007F00">'   Action if Condition(s) are met (do your thing here...)</SPAN><br>            Application.EnableEvents = <SPAN style="color:#00007F">False</SPAN><br>            <SPAN style="color:#007F00">'   Look for the Tracking #</SPAN><br>                strTracking = Columns("A").Find(Target.Value).Address<br>            <SPAN style="color:#007F00">'   If the Tracking # exists, then time stamp the original and delete the Out scan</SPAN><br>                <SPAN style="color:#00007F">If</SPAN> strTracking <> Target.Address <SPAN style="color:#00007F">Then</SPAN><br>                    Range(strTracking).Offset(, 2).Value = Format(Now, "mm/dd/yyyy hh:mm:ss AM/PM")<br>                    Target.EntireRow.Delete<br>                <SPAN style="color:#00007F">Else</SPAN><br>                <SPAN style="color:#007F00">'   If the Tracking # doesn't exist then enter it and the In time stamp</SPAN><br>                    Target.Offset(0, 1).Value = Format(Now, "mm/dd/yyyy hh:mm:ss AM/PM")<br>                <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">If</SPAN><br>            Application.EnableEvents = <SPAN style="color:#00007F">True</SPAN><br>            <br><SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN></FONT>

 

[cval]

WOW! Thanks for sticking with it - this is exactly what I was looking for. It seems you had to change what I had come up with by specifying the single cell value in the range (could not figure that part out). The (Target.Value).Address is the cell that a value is in correct? (I'm a little confused with that)

Again thanks for your help with this.

 

[Smitty]

It seems you had to change what I had come up with by specifying the single cell value in the range (could not figure that part out). The (Target.Value).Address is the cell that a value is in correct? (I'm a little confused with that)

There are 12 different ways to skin a cat with Excel, so this is just one approach, but what I did was to look for the target value entered in column A. If it's found, then append the Out time and delete the new row entered from the scanner, if not, then treat it as a new entry and allow it.

If you have any questions about the code, just repost it with comments above/after the lines in question and we'll do what we can to answer them.

 

[cval]

The code has been working great.

I only noticed one little "bug" when you input a matching barcode serial number it will skip a row. I added a line to prevent this.

If strTracking <> Target.Address Then
Range(strTracking).Offset(, 2).Value = Format(Now, "mm/dd/yyyy hh:mm:ss AM/PM")
Target.EntireRow.Delete
ActiveCell.Offset(-1, 0).Select
Else

Other then that it works like a charm!