Autofill Date Macro

• • Reply

  10 years ago

  by Christopher_M

  Chris Mungai

  Phoenix, United States

  Joined 10 years ago

  First up, I just want to say my level of knowledge is beginner. I have written some small VB programs ever since VB4, and I know a few excel funtions, but I'm a hobbyist at best.

  I'm working on a spreadsheet and using data entry via a hand barcode scanner. The scanner is programmed for auto enter have the scan is complete. So when I scan an asset barcode, it populates the cell and automatically sends an [enter] keystroke. Scan a barcode into A1, and the selsction moves down to A2, and so on. In the adjacent colum, B, I have to have a Date entered for the tracking of when the asset was put into record. This is a manual process that I am trying to automate.

  Now to help save time and expidite over all time spent in processing, I'm looking for a Macro or formula that will enter the current date into the adjacent cell, (B1 for data in A1; B2 for A2; and so on) and only for when it's been input. I don't want the sheet to change the date to the current day every time I open it on a following day.

  Not sure if a "IF/Then/Else" setup would work, but any help would be greatly appriciated.

  Thank you!

  Report abuse
• • Reply

  10 years ago

  by Christopher_M

  Chris Mungai

  Phoenix, United States

  Joined 10 years ago

  Found the solution:

  Private Sub Worksheet_Change(ByVal Target As Range)
  Dim keyRange As Range
  Set keyRange = Range("A:A")
  If Not Intersect(keyRange, Target) Is Nothing Then
  Target.Offset(0, 1) = Date
  End If
  End Sub
  

  This works perfectly for my needs.

  Report abuse