[VBA] How to Reproduce “Ctrl + F” Search Function | Usage of Find Method

Environment

• OS: Windows 10 Pro (Version: 20H2)
• Software: Microsoft Excel VBA

Background

I learned how to reproduce the “Ctrl + F” search function in Excel using VBA to find specific strings within cells.

Solution: Use the Find Method

In VBA, you can implement search behavior similar to “Ctrl + F” by using the Find() method.

Code Example (Searching for “Mori” in the Sheet)

Dim FoundCell As Range

Set FoundCell = Cells.Find(What:="Mori")

If FoundCell Is Nothing Then
    MsgBox "Not found."
Else
    FoundCell.Activate
End If

Code Explanation

• Line 1: Declare a variable named FoundCell as Range type.
• Line 2: Search for the string “Mori” within the entire sheet (Cells) and store the result in FoundCell.
• Line 3: Use an If statement to check if the search result is “not found (Nothing).”
• Line 4: If “Mori” is not found, display “Not found.” in a message box.
• Line 6: If “Mori” is found, move to (activate) that cell.
• Line 7: End the If statement.

Key Points

• Use Cells.Find to search the entire sheet.
• Specify the string you want to find in the What argument.
• Check if it was found using If FoundCell Is Nothing.
• If found, you can move to that cell using FoundCell.Activate.

Summary

To reproduce the “Ctrl + F” search in Excel VBA:

1. Use Cells.Find(What:="Search Word").
2. Use conditional branching for cases where the result is not found (Nothing) and where it is found.

By mastering this basic pattern, you can freely incorporate search functions into your macros.