Automate data entry (vba)
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 SubExample 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 SubWhere to copy code?
- Right click on current sheet name
- Click "View code"
- Copy/Paste vba code
Download example file *.xlsm
Related posts:
Categorize data entry values (vba)
Add values to a data validation list (vba)
Automate net asset value (NAV) calculation on your stock portfolio (vba) in excel
Recommended categories
Oscar,
I do remember seeing one nice way of populating a table with the use of vba such as :
Sub EnterName()
Dim col, Lrow As Single
Dim tmp As String
col = Application.WorksheetFunction.Match(Range("C2"), Range("B4:H4"), 0) - 1
tmp = Range("B" & Rows.Count).Offset(0, col).Address
Lrow = Range(tmp).End(xlUp).Row
Range("B1").Offset(Lrow, col).Value = Range("E2").Value
Range("B1").Offset(Lrow, col + 1).Value = Range("F2").Value
Range("E2").Value = ""
Range("F2").Value = ""
End Sub
How would it be possible to modify the code to populate a table such as: the first column header could be chosen from the drop-down list as well as the first row header. In other word the location of the data to be entered could be determined by the row AND the column.
Thanks to share your opinion.
Cyril,
What is the value in cell C2 and the values in cell range B4:H4?
C2 should be a data validation (list).
B4:H4 (here only 7 columns) would be the headers to match the value in C2.
A second data validation should make reference to Column A.
Hence:
1st data validation correlated to Column's Headers (B to ect)
2nd data validation correlated to values in Column A ("Row Header")
As for the kind of values, being headers they would most likely be (but not limited to) text strings.
Cyril,
See this post: Add values to a table (vba)