Forum Discussion

ljones5522's avatar
ljones5522
Copper Contributor
Jul 16, 2024

Enter a date in a cell after I have scanned a bar code

Hello, I am trying to add a date in column K after I have scanned the bar code label containing the TCN that is in column B.  Below is the VBA code that I am using to find the TCN and then highlights the row and adds that TCN to column N.  I like to add the date to column K for each day that I scan.  Thank you. 

Private Sub Worksheet_Change(ByVal target As Range)
If Not Intersect(target, Columns("M")) Is Nothing Then
Z = Intersect(target, Columns("M")).Value
If IsNumeric(Z) Then
x = Application.Evaluate("MATCH(" & Z & ",B:B,0)")
Else
x = Application.Evaluate("MATCH(" & Chr(34) & Z & Chr(34) & ",B:B,0)")
End If
If Not IsError(x) Then
Application.Goto Cells(x, 15)
End If

Macros and VBA

1 Reply

  • AshaKantaSharma's avatar
    AshaKantaSharma
    Iron Contributor
    Aug 20, 2024
    Private Sub Worksheet_Change(ByVal target As Range)
    Dim Z As Variant
    Dim x As Variant
    Dim matchedRow As Long

    ' Check if the change is in column M
    If Not Intersect(target, Columns("M")) Is Nothing Then
    Z = Intersect(target, Columns("M")).Value

    ' Find the row number where the TCN (in column M) matches column B
    If IsNumeric(Z) Then
    x = Application.Evaluate("MATCH(" & Z & ",B:B,0)")
    Else
    x = Application.Evaluate("MATCH(" & Chr(34) & Z & Chr(34) & ",B:B,0)")
    End If

    ' If a match is found
    If Not IsError(x) Then
    ' Highlight the matching row
    Application.Goto Cells(x, 15)

    ' Add TCN to column N of the matching row
    Cells(x, 14).Value = Z ' Assuming column N is the 14th column

    ' Add the current date to column K of the matching row
    Cells(x, 11).Value = Date ' Assuming column K is the 11th column
    End If
    End If
    End Sub

Resources