Fix Word VBA Find and Replace Problems with Apostrophes
Question details
The user needs to resolve an issue where Word VBA Find and Replace macros fail to locate text containing apostrophes or quotation marks.
- Product
- Microsoft Word / WPS Writer
- Device & OS
- not provided
- Scenario
- Running a VBA macro to search for contractions or quoted text in a document.
- Observed behavior
- The VBA search fails because straight apostrophes used in the code do not match the typographic (smart) apostrophes present in the document text, which are often automatically altered by AutoCorrect.
Check your document to see if the text uses straight apostrophes (') or curved typographic apostrophes (’), as VBA reads these as completely different characters during a search.
Use Exact Typographic Characters in Your VBA Code
Ensure your VBA search string explicitly contains the exact smart quote or apostrophe character used in the target document.
Word's AutoCorrect automatically converts straight quotation marks and apostrophes into typographic (smart) versions. Because the VBA Find object searches for exact character matches, searching for a straight apostrophe will fail to locate a smart apostrophe in the text.
To fix this, you must input the exact typographic character into your VBA code using Alt codes.
Press Alt + F11 to open the VBA Editor and locate your Find and Replace macro code.
In your search string, delete the straight apostrophe. Hold down the Alt key and type 0146 on your numeric keypad to insert a closing typographic apostrophe (smart single quote).
If you are searching for other quotation marks, use Alt + 0145 for an opening single quote, Alt + 0147 for an opening double quote, or Alt + 0148 for a closing double quote.
Save your code and run the macro again. The Find object will now successfully match the typographic apostrophes in your document.
Disable Smart Quotes AutoCorrect Before Drafting Code
Prevent the word processor from changing quotes into smart quotes if you draft your VBA code in a document before copying it to the editor.
Try WPS Office for a Streamlined Document Experience
If you frequently encounter complex macro issues and formatting inconsistencies in Microsoft Word, WPS Office provides a highly compatible, lightweight, and easy-to-use alternative. It offers excellent formatting control and seamlessly handles all your Word documents without the steep learning curve.
- 1. Download WPS Office: Visit the official WPS website and download the free installer for your operating system.
- 2. Install the Software: Run the setup file and follow the on-screen prompts to complete the installation in minutes.
- 3. Open Your Documents: Launch WPS Writer and open your existing Word documents. Your formatting and text will remain perfectly intact.
Frequently Asked Questions
Why does my VBA find and replace macro skip some contractions?
Your document likely contains a mixture of straight apostrophes and smart (typographic) apostrophes. Because the VBA Find object requires an exact character match, searching for a straight apostrophe will cause the macro to skip any contractions formatted with smart apostrophes.
How do I type a smart quote directly into the VBA Editor?
You can insert smart quotes into the VBA Editor by using Alt codes on a numeric keypad. Hold the Alt key and type 0146 for a closing single quote (apostrophe), 0145 for an opening single quote, 0147 for an opening double quote, or 0148 for a closing double quote.
Can I turn off the feature that changes my quotation marks automatically?
Yes. You can disable this by going to your word processor's Options menu, navigating to Proofing > AutoCorrect Options > AutoFormat As You Type, and unchecking the 'Straight quotes with smart quotes' option.
