In some cases it can be useful and timesaving to automate data entering. The vba examples here all enter a value or formula in a cell if a value is entered by the user in an adjacent column.

Example 1 - Time stamp

Enter a name in column A and current date and time is entered automatically in column B. You can also copy a cell range and paste in column A. Empty cells are not processed.

VBA code

Private Sub Worksheet_Change(ByVal Target As Range)
Dim Value As Variant
If Not Intersect(Target, Range("A:A")) Is Nothing Then
    For Each Value In Target
        If Value <> "" Then
            Range("B" & Value.Row).Value = Now
        End If
    Next Value
End If
End Sub

Example 2 - Formula

Enter a price in column B and a formula is instantly entered in column C.

Formula in column c: Cell value in column B multiplied by 1.1

VBA code

Private Sub Worksheet_Change(ByVal Target As Range)
Dim Lrow As Single
Dim AStr As String
Dim Value As Variant
If Not Intersect(Target, Range("B:B")) Is Nothing Then
    For Each Value In Target
        If Value <> "" Then
            Range("C" & Value.Row).Formula = "=" & Target.Address & "*1.1"
        End If
    Next Value
End If
End Sub

Where to copy code?

  1. Right click on current sheet name
  2. Click "View code"
  3. Copy/Paste vba code

Download example file *.xlsm

Automate data entry.xlsm