Hello! Today, we will look at how to use VBA (Visual Basic for Applications) to extract specific characters in Excel and how to use them. Using VBA allows us to significantly expand the capabilities of Excel. Below, I will present three practical examples for Excel beginners.
Example 1: Searching and Highlighting Cells that Contain a Specific String
In this example, we will explain how to automatically find all cells that contain a specific string and change their color to make them stand out.
| Step | Description |
|---|---|
| 1. Open the VBA Editor | Press “Alt + F11” in Excel to open the VBA Editor. |
| 2. Create a New Macro | Select “Insert” from the menu and choose “Module” to create a new macro. |
| 3. Write the Code | Paste the following code into the module:
Sub HighlightCells()
Dim Cell As Range
For Each Cell In Selection
If InStr(Cell.Value, "specific string") > 0 Then
Cell.Interior.Color = vbYellow
End If
Next Cell
End Sub
|
| 4. Run the Macro | Select the cells that contain the specific string and run the created macro. |
Example 2: Extracting Data that Matches Specific Criteria to a New Sheet
Next, we will show how to find rows that meet certain criteria and automatically copy them to a new worksheet.
| Step | Description |
|---|---|
| 1. Open the VBA Editor | Similarly, press “Alt + F11” to open the VBA Editor. |
| 2. Create a New Macro | From the “Insert” menu, select “Module” to create a new macro. |
| 3. Write the Code | Paste the following code into the module:
Sub CopyMatchingData()
Dim SourceSheet As Worksheet
Dim TargetSheet As Worksheet
Dim LastRow As Long
Dim MatchingRow As Long
Set SourceSheet = ThisWorkbook.Sheets("Source Sheet Name")
Set TargetSheet = ThisWorkbook.Sheets.Add(After:=ThisWorkbook.Sheets(ThisWorkbook.Sheets.Count))
TargetSheet.Name = "Extracted Data"
LastRow = SourceSheet.Cells(SourceSheet.Rows.Count, "A").End(xlUp).Row
MatchingRow = 1
For i = 1 To LastRow
If SourceSheet.Cells(i, 1).Value = "specific criteria" Then
SourceSheet.Rows(i).Copy Destination:=TargetSheet.Rows(MatchingRow)
MatchingRow = MatchingRow + 1
End If
Next i
End Sub
|
| 4. Run the Macro | Run the macro to copy the data that matches the criteria to a new sheet. |
Example 3: Classifying Data Based on Specific Keywords
Lastly, we will see how to automatically classify data that contains specific keywords into different categories.
| Step | Description |
|---|---|
| 1. Open the VBA Editor | Open the VBA Editor with “Alt + F11”. |
| 2. Create a New Macro | Select “Insert” and then “Module” to create a new macro. |
| 3. Write the Code | Paste the following code into the module:
Sub ClassifyData()
Dim Cell As Range
Dim CategoryRange As Range
Set CategoryRange = ThisWorkbook.Sheets("Category Sheet").Range("A1:A10") ' Range listing the categories
For Each Cell In Selection
For Each Cat In CategoryRange
If InStr(Cell.Value, Cat.Value) > 0 Then
Cell.Offset(0, 1).Value = Cat.Value
Exit For
End If
Next Cat
Next Cell
End Sub
|
| 4. Run the Macro | Select the cells with the data and run the macro to classify them based on categories. |
Using VBA can significantly improve your efficiency in Excel. Through these examples, I hope you become more familiar with Excel operations and acquire more advanced skills.