Automate data entry [VBA]
This article demonstrates how to automatically enter data in cells if an adjacent cell is populated using VBA code.
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 the adjacent column.
The image above shows a worksheet that enters a timestamp in column B if the corresponding cell in column A is populated.
VBA enters timestamp automatically in the adjacent cell
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 them to column A. Empty cells are not processed.
What makes this work is an event code that catches changes on a worksheet, this makes it possible to populate the adjacent cell with whatever you want. I am in this case entering a time stamp in column B.
VBA code
'Event procedure that is rund if any cell on the worksheet is changed Private Sub Worksheet_Change(ByVal Target As Range) 'Dimension variable and data type Dim Value As Variant 'If target cell (the cell that changed) is in column A then do this. If Not Intersect(Target, Range("A:A")) Is Nothing Then 'Repeat these lines as many times as there were cells that changed For Each Value In Target 'If value is not equalt to nothing then do this. If Value <> "" Then 'Populate adjacent cell in column B based on row number from target cell Range("B" & Value.Row).Value = Now End If Next Value End If End Sub
Where to copy code?
- Press with right mouse button on on the sheet name
- Press with left mouse button on "View code" to open the VB Editor.
- Copy/Paste vba code to sheet module, see image above.
Populate a cell with a formula using VBA
The following VBA code populates the adjacent cell in column C with a formula if corresponding cell value in column B is populated.
Here is what I did: 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
'Event code that runs if a cell or cell range is changed Private Sub Worksheet_Change(ByVal Target As Range) 'Dimension variables and declare data types Dim Lrow As Single Dim AStr As String Dim Value As Variant 'Check if cell or cell range that changed is in column B If Not Intersect(Target, Range("B:B")) Is Nothing Then 'Repeat the following code as many times as there a cells in the cell range that changed For Each Value In Target 'Check if cell (that changed) is not equal to nothing If Value <> "" Then 'Populate adjacent cell in column C with a formula Range("C" & Value.Row).Formula = "=" & Target.Address & "*1.1" End If Next Value End If End Sub
Where to copy code?
- Press with right mouse button on on current sheet name
- Press with left mouse button on "View code"
- Copy/Paste vba code to sheet module.
- Copy/Paste vba code to sheet module, see image above.
Final notes
The techniques demonstrated here in this article should give you a great start in automating data entry in your workbook.
Keep in mind that if you happen to get caught in an endless loop because the code changes a value that then triggers the event code over and over you can prevent this by using the Application.EnableEvents property.
Application.EnableEvents = False Code Application.EnableEvents = True
This will stop the event code from triggering while the macro manipulates the worksheet.
The first example above automatically enters the timestamp, however, if want to know how to manually enter a timestamp then use following shortcut keys: CTRL + :
The second example can be done using formulas as well, you can also use a formula that will output nothing if the adjacent value is empty.
Formula in cell C2:
This formula will check if cell B2 is not empty and then multiply that value with 1.1 if TRUE and return a blank if FALSE.
B2<>"" is a logical expression that returns either TRUE or FALSE. If B2 is not empty the logical expression returns TRUE and if it is empty the logical expression returns FALSE.
IF(logical_expression, if_true, if_false)
The IF function then runs the second argument if the logical expression returns TRUE and the third argument if FALSE.
You can read more about this function here: IF function
Macro category
This article demonstrates a macro that copies values between sheets. I am using the invoice template workbook. This macro copies […]
This tutorial shows you how to list excel files in a specific folder and create adjacent checkboxes, using VBA. The […]
In this post I am going to show how to create a new sheet for each airplane using vba. The […]
Functions in this article
More than 1300 Excel formulas
Excel categories
8 Responses to “Automate data entry [VBA]”
Leave a Reply
How to comment
How to add a formula to your comment
<code>Insert your formula here.</code>
Convert less than and larger than signs
Use html character entities instead of less than and larger than signs.
< becomes < and > becomes >
How to add VBA code to your comment
[vb 1="vbnet" language=","]
Put your VBA code here.
[/vb]
How to add a picture to your comment:
Upload picture to postimage.org or imgur
Paste image link to your comment.
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)
Hi Oscar,
Is there any way I can automatically populate the entry of one column base on the value of the other the value will be from the list
sample list:
Category Values
A a.1
a.2
a.3
B b.1
b.2
so on...
when i choose Category A in the cell A1 the the cell b1-b3 will be populted base on the values.
Thansk,
How do I automate a data entry for example
teacher- $50
Pets-$80
where as the pet is always $80 and the teacher is always $50
How would I enter or put a formula in for excel?
Sarah,
The lookup table is in cell range D1:E3.
I could not make the VBA for automated data entry to work.
So I figured out formula that works for me:
=IF(ISBLANK(A1)," ",NOW())
you just copy it down in the column you want the automated date and time, also you need to format the cell to custom:
d-mmm-yy-hh:mm AM/PM
or any date and time combined format.
and by the way Oscar is a genius, this blog saved me so much stress it is unbelievable.