MightyGhost
New Member
- Joined
- Nov 20, 2021
- Messages
- 1
- Office Version
- 365
- Platform
- Windows
Morning
I am new to VBA programming and what I am trying to do is scan a barcode that is already in a cell in Column E and have a scan in time and a scan out time in columns I and J. But then if that same barcode gets taken out again it be registered on a new row so I can have multiple scans of the same barcode I have attached a test sheet it would be really helpful if can assist
Here is the code that I am trying to edit and use
I am new to VBA programming and what I am trying to do is scan a barcode that is already in a cell in Column E and have a scan in time and a scan out time in columns I and J. But then if that same barcode gets taken out again it be registered on a new row so I can have multiple scans of the same barcode I have attached a test sheet it would be really helpful if can assist
Here is the code that I am trying to edit and use
VBA Code:
Sub access()
Dim barcode As String
Dim rng As Range
Dim rownumber, erow As Long
Dim r As Long
Dim Total As Double
Dim Timein As Date
Dim Timeout As Date
Dim ws As Worksheet
Dim name As String
erow = ActiveSheet.Cells(Rows.Count, 1).End(xlUp).Row + 1
Set ws = Sheet1
barcode = Sheet1.Cells(2, 1)
If barcode (not equal)"" Then
With Sheet1.Range("a4:a10000")
Set rng = .Find(what:=barcode, _
LookIn:=xlFormulas, LookAt:=xlWhole, SearchOrder:=xlByRows, _
searchdirection:=xlNext, MatchCase:=False, SearchFormat:=False)
If rng Is Nothing Then
restart:
Sheet1.Cells(erow, 1).Select
ActiveCell.Value = barcode
ActiveCell.NumberFormat = "@"
ActiveCell.Offset(0, 1).Select
ActiveCell.Value = Date & " " & Time
ActiveCell.NumberFormat = "d/m/yyyy h:mm AM/PM"
ActiveSheet.Cells(2, 1) = ""
GoTo ende
Else
If Not rng Is Nothing Then
rownumber = rng.Row
If Cells(rownumber, 3) not equal "" Then
For r = rownumber To erow
If Cells(r, 1) = barcode And Cells(r, 3) = "" Then
rownumber = r
GoTo enterdata
End If
Next r
GoTo restart
End If
enterdata:
ActiveSheet.Cells(rownumber, 1).Select
ActiveCell.Offset(0, 2).Select
ActiveCell.Value = Date & " " & Time
ActiveCell.NumberFormat = "m/d/yyyy h:mm AM/PM"
Timein = CDate(Cells(rownumber, 2).Value)
Timeout = CDate(Cells(rownumber, 3).Value)
Sheet1.Cells(2, 1) = ""
End If
End If
End With
Sheet1.Cells(2, 1) = ""
'
End If
ende:
Sheet1.Activate
Sheet1.Cells(2, 1).Select
End Sub
Last edited by a moderator: