VBA to populate asset, location, time in, time out. And update column in table

[rdo556]

Hello all,

I know MS Access would be better for this but excel is what I have to work with.

I am trying to create a macro that will allow me to scan the bar code serial number of an asset and log it in cell A2, then scan the location Out to B2, Check out, to C2, and Check in, to D2, Location In to E2. next scanned asset would go in B2 , C2, ect...

I would also like for it to update a table field in another worksheet when the asset is checked out or checked In.

Worksheet "Inventory" Table "tblData" column "Location"

I found this in my search and it almost works, minus the location fields.

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

[TABLE="class: grid, width: 653"]
<tbody>[TR]
[TD]Serial Number[/TD]
[TD]Location Out[/TD]
[TD]Checked Out[/TD]
[TD]Checked In[/TD]
[TD]Location In[/TD]
[/TR]
[TR]
[TD]B-005[/TD]
[TD]ROTHE[/TD]
[TD="align: right"]8/22/2017 16:57[/TD]
[TD="align: right"]8/23/2017 4:51[/TD]
[TD]TOOL CRIB[/TD]
[/TR]
[TR]
[TD]GM-011[/TD]
[TD]CAL-TECH[/TD]
[TD="align: right"]8/22/2017 16:57[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]P-067[/TD]
[TD]MACHINE 15[/TD]
[TD="align: right"]8/22/2017 16:57[/TD]
[TD="align: right"]8/23/2017 8:45[/TD]
[TD]TOOL CRIB[/TD]
[/TR]
</tbody>[/TABLE]


Thanks in advance for any and all assistance.

Ryan