Experts teach you how to use VBA code to quickly enter Excel data

When entering data in an Excel spreadsheet with a large number of columns, pulling the scroll bar back and forth is both cumbersome and easy to go wrong, which is very inconvenient. So how do you quickly enter data in Excel? In fact, there are a lot of techniques for entering data in Excel, and using them reasonably can improve our work efficiency. This article takes a different approach and introduces you how to use VBA code to achieve fast data entry. Please see the example below.

Suppose that in the column of data that is filled in, the department to which the employee belongs has "a workshop", "two workshops", "sales department", etc., if we can replace these with numbers If the text content is entered (as shown in the figure), it will greatly improve the efficiency of our entry.

According to this idea, we entered the following code in the Change event of the worksheet:

Private Sub Workheet_Change(ByVal Target As Range)

If Target.Column = 2 Then ’This function is only implemented in the second column

If Target.Value = 1 Then

Target.Value = "One Workshop"

Else< Br>

If Target.Value = 2 Then

Target.Value = "Second Workshop"

Else

If Target.Value = 3 Then

Target.Value = "Sales"

End If

End If

End If

End If

End Sub

Return to the Excel window, and enter the corresponding number to be replaced with the specified text content. Some friends may think that it is not OK to enter the data first and then use the search to replace it. However, we will find that it is easy to replace the data in other columns by the search and replace operation. If each number is confirmed and then replaced, it will be more cumbersome, and the code will not appear. After loading, It can be done once and for all.

Tip: The code in the code "Target.Column = 2" is used to specify that the data can only be entered in the second column (if not specified for the entire worksheet), so as to ensure that the other Columns entering this data will not be affected. Similarly, we can use code to control the fast entry of text in other columns. This technique is suitable for pre-loading into a form or programmed into a program for people who are not familiar with Excel, so that we can quickly fill out the form filled out by the employee (tick the corresponding numbered content or directly fill in the number of the specified content). Collect data.