VMware

Quickly Enter Dates in Cells After Scanning Barcodes with Microsoft Community Hub

Sure! Here’s the new blog post based on the information provided:

As a business owner, keeping track of inventory and supplies can be a daunting task. One way to make this process easier is by using Microsoft Excel to create a spreadsheet that automatically updates when items are scanned. In this blog post, we will go over how to use VBA code to highlight the row of the scanned item and add the date to a separate column.

First, let’s take a look at the code provided by Gaye Dougherty:

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)”)

Elsex = 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

End Sub

This code scans the barcode of an item in column B and then highlights the row of the scanned item in yellow. It also adds the TCN (TC Number) of the item to column N. However, it does not add the date to a separate column.

To add the date to a separate column, we can use the TODAY() function in VBA. Here’s the updated code:

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)”)

Elsex = Application.Evaluate(“MATCH(” & Chr(34) & Z & Chr(34) & “,B:B,0)”)

End If

If Not IsError(x) Then

Application.Goto Cells(x, 15)

Date = TODAY()

Cells(x, 16).Value = Date

End If

End Sub

In this updated code, we’ve added a new column K and used the TODAY() function to add the current date to each row that is scanned. Now, when an item is scanned, not only will the row be highlighted in yellow and the TCN be added to column N, but the current date will also be added to column K.

Using this code, you can easily keep track of inventory and supplies, and even identify which items were scanned on a particular day. This can help you manage your stockroom more efficiently and make informed decisions about restocking and ordering.

We hope this blog post has been helpful in showing you how to use VBA code to add the date to a separate column when scanning items with a barcode. If you have any questions or need further assistance, please don’t hesitate to reach out.