VBA & Macro Problems

How to Use Excel VBA Input Box to Search for Exact Text

Khadija Khan Oct 22, 2026 870 views

Question details

The user needs a VBA macro that prompts for a search string via an InputBox, finds exact matches in a column, and copies the value from the row immediately below each match to a destination sheet.

How to Use Excel VBA Input Box to Search for Exact Text

Product: Excel
Device & OS: not provided
Scenario: Automating data extraction where a specific name or company needs to be located exactly, and related data stored in the following row must be dynamically copied to another worksheet.
Observed behavior: The user requires a custom VBA script to reliably capture user input, loop through exact matches without returning partial matches, and copy targeted row offsets seamlessly.

Before you start

Ensure you have saved your workbook as a Macro-Enabled Workbook (.xlsm) and that the Developer tab is visible in your ribbon so you can access the VBA Editor.

Solution 1Recommended

Use VBA InputBox and Range.Find for Exact Matches

This solution uses an InputBox to capture the user's search term and applies the Range.Find method with the LookAt:=xlWhole parameter to ensure only exact matches are processed.

By utilizing the LookAt:=xlWhole argument within the Range.Find function, you prevent the macro from falsely identifying partial text strings. A loop is then used to iterate through all matches and copy the adjacent offset data.

Open the VBA Editor

Press ALT + F11 to open the Visual Basic for Applications (VBA) editor. Click 'Insert' > 'Module' to create a new blank module for your code.

Set up the InputBox

Declare a string variable and use the InputBox function to ask the user for the search term: strText = InputBox(Prompt:="Enter the text to search for"). Add an IF statement to exit the sub if the input is left blank.

Configure the Exact Match Search

Use the Range.Find method on your target column, such as Sheets(1).Range("A:A"). It is crucial to set LookAt:=xlWhole to ensure the macro only stops on exact matches.

Loop Through Matches and Copy Offsets

Create a Do...Loop to cycle through all found matches until the address loops back to the first find. Use cell.Offset(1, 0).Value to grab the data from the row immediately following the exact match, and write it to your destination sheet.

Run the Macro

Close the VBA editor, return to your spreadsheet, and run the macro from the Developer tab under 'Macros'. The InputBox will appear, prompting you for your exact text search.

Use VBA InputBox and Range.Find for Exact Matches

Complete VBA Code Snippet: Sub CopyNames() Dim strText As String, cell As Range, strAddress As String, destRow As Long strText = InputBox("Enter the text to search for") If strText = "" Then Exit Sub Application.ScreenUpdating = False With Sheets(1).Range("A:A") Set cell = .Find(What:=strText, LookAt:=xlWhole) If Not cell Is Nothing Then strAddress = cell.Address Do destRow = destRow + 1 Sheets("Members").Cells(destRow, "A").Value = cell.Offset(1, 0).Value Set cell = .Find(What:=strText, After:=cell, LookAt:=xlWhole) If cell Is Nothing Then Exit Do Loop Until cell.Address = strAddress End If End With Application.ScreenUpdating = True End Sub

Advanced Macro Support

Execute VBA Macros Seamlessly in WPS Office

WPS Office fully supports VBA macros, allowing you to run your exact-text search scripts and automate complex spreadsheet tasks just like in Microsoft Excel.

1. Download and Install: Download and install WPS Office on your computer, ensuring you have the VBA macro module enabled.
2. Open Your Workbook: Open your existing Excel Macro-Enabled Workbook (.xlsm) directly in WPS Spreadsheet.
3. Access Developer Tools: Navigate to the 'Developer' tab on the ribbon to access the built-in VBA Editor.
4. Run Your Script: Paste your exact match search VBA script into a module and execute it seamlessly.

Fully compatible with Microsoft Excel VBA syntax and objectsLightweight application with fast, reliable macro executionBuilt-in Developer tools for editing and debugging codeFree basic features with accessible advanced automation tools

microsoft office alternative - wps office

Frequently Asked Questions

What happens if the search text is not found by the macro?

The macro relies on 'If Not cell Is Nothing' to check if a match exists. If no exact match is found for your inputted text, the macro will cleanly bypass the copying process and finish without producing any errors.

How do I change the macro to search a different column?

Locate the 'With Sheets(1).Range("A:A")' reference in the VBA script and update the column letter to your target column. For example, change it to 'Range("B:B")' to search column B.

What if I want to search for partial text matches instead?

In the 'Range.Find' method, change the parameter from 'LookAt:=xlWhole' to 'LookAt:=xlPart'. This adjustment allows the macro to find and process cells that contain the search string as part of a larger text.

How can I copy the value from the adjacent column instead of the row below?

You can modify the offset parameters in the code. Change 'cell.Offset(1, 0).Value' (which moves down 1 row) to 'cell.Offset(0, 1).Value' to copy data from the column immediately to the right of your matched cell.

Excel & Data Problems