Forum Discussion

marvel2r's avatar
marvel2r
Copper Contributor
May 13, 2022
Solved

Excel Visual Basic help

Hello. Was wondering if you could help me with a workbook I'm trying to create. The purpose is to track employees with numbers; on the first sheet (Form) someone will scan their number, it will then ...
excel
Formulas and Functions
Macros and VBA
  • HansVogelaar's avatar
    HansVogelaar
    May 14, 2022

    marvel2r 

    Try

    Sub inout()
    Dim ws1 As Worksheet
    Dim ws2 As Worksheet
    Dim barcode As String
    Dim rng As Range
    Dim rownumber As Long
    Set ws1 = Worksheets("Form")
    ' *** Change Sheet2 to the name of the other sheet ***
    Set ws2 = Worksheets("Sheet2")
    barcode = ws1.Range("B2").Value
    Set rng = ws2.Range("A:A").Find(What:=barcode, LookIn:=xlValues, LookAt:=xlWhole)
    If rng Is Nothing Then
        Set rng = ws2.Range("A" & ws2.Rows.Count).End(xlUp).Offset(RowOffset:=1)
        rng.Value = barcode
    End If
    rownumber = rng.Row
    Set rng = ws2.Cells(rownumber, ws2.Columns.Count).End(xlToLeft).Offset(ColumnOffset:=1)
    rng.Value = Now
    rng.NumberFormat = "m/d/yyyy h:mm AM/PM"
    ws1.Range("B2").ClearContents
End Sub
marvel2r's avatar
marvel2r
Copper Contributor
May 14, 2022
Or how can I edit this code so when a previously scanned number is scanned again it will find the row and add a timestamp one cell to the right of the first timestamp it did?


Private Sub Worksheet_Change(ByVal Target As Range)

If Intersect(Target, Range("D16")) Is Nothing Then Exit Sub
If Target = "" Then Exit Sub
Dim w2 As Worksheet, nr As Long
With Application
.EnableEvents = False
.ScreenUpdating = False
Set w2 = Sheets("Sheet2")
With w2
nr = w2.Cells(w2.Rows.Count, "A").End(xlUp).Row + 1
If nr = 2 And w2.Range("A1") = vbEmpty Then nr = 1
w2.Range("A" & nr) = Target.Value
w2.Range("B" & nr) = Now()
w2.Range("B" & nr).NumberFormat = "mm/dd/yyyy hh:mm:ss AM/PM"
w2.Columns("A:B").AutoFit


End With
Target.Clear
.EnableEvents = True
.ScreenUpdating = True
End With
End Sub