## 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 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?**

- Right click on current sheet name
- Click "View code"
- Copy/Paste vba code

**Download example file *.xlsm**

### Category: Excel

5 easy ways to VLOOKUP and return multiple values

This post explains how to lookup a value and return multiple values. No array formula required.Comments(445) Filed in category: Excel, VLOOKUP and return multiple values

Lookup and return multiple values concatenated into one cell

This article demonstrates how to find a value in a column and concatenate corresponding values on the same row. The […]Comments(249) Filed in category: Concatenate, Excel, Textjoin

Comments(161) Filed in category: Charts, Excel, Interactive

### 8 Responses to “Automate data entry (vba)”

### Leave a Reply

**How to add a formula to your comment:**

<code>your formula</code>

**How to add VBA code to your comment:**

[vb 1="vbnet" language=","]

VBA code

[/vb]

**How to add a picture to your comment:**

Upload picture to postimage.org

Add picture link to comment.

**Contact Oscar**

You can contact me through this webpage

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.