January 9, 2022

Improve Productivity During Data Entry with Excel VBA

How to improve productivity during data entry in Excel with VBA. Watch the video below:

Improve Productivity while entering using Excel VBA

Let’s learn how to improve our productivity while entering data into an Excel worksheet. So here we have data of different PAN (Permanent Account Numbers) numbers. These are numbers that people use in India while filing their income tax returns. Each of these numbers is of 10 characters consisting of alphabets and numbers. We can check the length of the entries using the LEN function. In a similar manner we can have social security numbers (11 characters) of a specific length. Order numbers in a company or roll number of students can also be of specific length. Now we can enter such data on by one in different cells of an Excel worksheet or we can enter the data in a single cell without any spaces. Once the data is entered in a single cell we can use a macro to separate each of the PAN numbers, for example, into different cells automatically. To make the macro code user-friendly we can use an input box to ask the user how many characters does each of his PAN or Social Security Numbers has.

Here is the complete macro code to automate the process of improving productivity in Excel during data entry:

Sub TenCharEntry()
Dim str As String
Dim x As Integer
Dim y As Integer
Dim i As Integer
str = ActiveCell.Value
i = InputBox(“Enter the number of characters to be extracted”)

y = 0
For x = 1 To Len(str) Step i
ActiveCell.Offset(y, 0) = Mid(str, x, i)
y = y + 1
Next
End Sub

Improve Productivity while entering using Excel VBA
Improve Productivity with Excel VBA

Further Reading:

Inputbox Function

MID function

Leave a Reply

Your email address will not be published.

This site uses Akismet to reduce spam. Learn how your comment data is processed.