How to Use Partial Matching with VBA Find in Excel
Question details
The user needs to search for cells that contain a specific substring rather than an exact full-cell match using VBA.
- Product
- Excel
- Device & OS
- not provided
- Scenario
- Automating a search task using Excel VBA where the target value is only part of the cell's contents, such as locating the word 'Business' inside 'Business Operations'.
- Observed behavior
- The default or current VBA Find configuration requires an exact match, failing to locate cells that contain additional text alongside the search string.
Ensure your VBA editor is open (press Alt + F11) and locate the specific macro containing the Find method you wish to modify.
Modify the VBA Find Method Argument to xlPart
Change the LookAt argument in your VBA code to allow the macro to match cells containing the specified text anywhere within the cell.
The VBA Find method often defaults to the last used search setting, which might be set to match entire cell contents. By explicitly defining the LookAt argument in your code, you ensure the macro always performs a partial match regardless of previous searches.
Press Alt + F11 on your keyboard to open the Visual Basic for Applications (VBA) editor in Excel.
Find the module and the specific line of code where your Find method is executed.
Change the argument LookAt:=xlWhole to LookAt:=xlPart. If the LookAt argument is missing entirely, add , LookAt:=xlPart to your Find method parameters.
Save your changes and run the macro to verify that it successfully highlights or processes cells containing the partial string.
Run VBA Macros Seamlessly in WPS Office
WPS Office provides robust support for VBA macros, allowing you to execute advanced scripting and partial matching tasks exactly like Microsoft Excel. Automate your workflow in a highly compatible environment.
- 1. Open your file in WPS: Launch WPS Spreadsheet and open your macro-enabled workbook.
- 2. Access the Developer Tools: Navigate to the Developer tab on the ribbon and click on the VBA Editor icon.
- 3. Modify the code: Locate your Find function and ensure the parameter is set to LookAt:=xlPart.
- 4. Execute the macro: Run the script directly within WPS Spreadsheet to successfully find your partial matches.
Frequently Asked Questions
Why is my VBA Find macro still looking for exact matches?
If the LookAt parameter is not explicitly defined in your code, Excel VBA inherits the setting from the last manual or programmatic search. Always declare LookAt:=xlPart to force partial matching.
Can I make the partial match case-sensitive?
Yes, you can enforce case sensitivity by adding the MatchCase argument to your Find method. Set it as MatchCase:=True to distinguish between uppercase and lowercase letters.
How do I find the next partial match in VBA?
To find subsequent partial matches, use the FindNext method in your VBA code after the initial Find method. This will continue searching the range using the previously defined xlPart parameter.
