Autofill Date Macro

VBA , Excel , Macro Phoenix, United States
  • 8 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!

  • 8 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.

Post a reply

Enter your message below

Sign in or Join us (it's free).

Contribute

Why not write for us? Or you could submit an event or a user group in your area. Alternatively just tell us what you think!

Our tools

We've got automatic conversion tools to convert C# to VB.NET, VB.NET to C#. Also you can compress javascript and compress css and generate sql connection strings.

“Any fool can write code that a computer can understand. Good programmers write code that humans can understand.” - Martin Fowler