Excel VBA: Find Text

Save

Learning how to find text with Excel's programming language, VBA or Visual Basic for Applications, is useful for finding specific information quickly in large, unwieldy spreadsheets; for example, use the find text function to quickly locate specific addresses, names or phone numbers in a mailing list. Access VBA from the Excel Developer tab. If that tab doesn't appear, you may need to check Excel options to turn on its display.

An easy way to understand how to find text with VBA is by using Excel's macro recorder to create VBA statements by using the Home tab's Find command. Type some text in a worksheet cell, such as "abc," then click any other cell. Click the Developer tab's Record button, then press the Ctrl-F keys simultaneously to display the program's Find dialog box. Type some characters from the text you typed into the cell. Click Find to navigate to the cell, then click the Stop recording button on the Developer tab to end the macro recording.

After you've recorded a VBA macro for finding text, begin to understand how it works by reading its programming statements. Display the list of macros in your current workbook by clicking the Developer tab's Macros button, then clicking the name of your text-searching macro. For example, click TextFinder if that's the name of your macro, then click the Edit button to display the VBA programming statements for TextFinder.

VBA searches for text in the cells of the current worksheet with the Find function, which is a member of the Cells collection. This collection represents the cells in the current worksheet. You can get a good understanding of the arguments this function needs by examining the statements of a macro that searches for text. If you've recorded a macro that seeks the text "abc," your macro will resemble the following:

Sub FindText()

 Cells.Find(What:="abc", After:=ActiveCell, LookIn:=xlFormulas, LookAt:= _
    xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False _
    , SearchFormat:=False).Activate

End Sub

The What argument of the VBA Find command that searches for text, holds the text you want to search for. Excel will search for any text you specify for this argument. "What" is the only argument that the "Find" command requires. All other arguments are optional. You can therefore search for the text "abc," for example, with the statement "Cells.Find (What:="abc").activate. If you need "Find" to match a specific case for the "What" text, indicate that by setting the "MatchCase" argument to "True." This argument is "False" by default.

The Find command for locating text with VBA doesn't automatically navigate to any cells containing the text you're searching for. To make it do so, use the Activate method of the return value from Find. That return value is a Range object that represents the cells in which Find found the text you were seeking. For example, if you've searched for "abc" with Find, and only one cell had that text, the return value from Find would be a single-cell range. Running that cell's Activate function will make that cell the active cell.

References

Promoted By Zergnet

Comments

Related Searches

Check It Out

Geek Vs Geek: Robot battles, hoverboard drag race, and more

M
Is DIY in your DNA? Become part of our maker community.
Submit Your Work!