1
votes

I am at a loss to understand why the While...Wend loop is not working in this macro. Admittedly, While...Wend is not my strong suit, so there's that. But anyway, here's what I'm trying to accomplish:

I will have a list of words or phrases at the end of a document (each on a separate line). I want the macro to search for each of those words in the document and applying yellow highlighting. So here's the process I'm trying to create:

  1. Presume I am on the first word when I invoke the macro.
  2. Evaluate the first character. If it's an empty line, do nothing and quit the macro; if there is a word or phrase, select it, store it as FRText.
  3. Delete the line, create a bookmark, use F/R to find and highlight the text.
  4. Rinse and repeat until there are no more words.

I have broken down the macro into single-step increments in temp macros, and I've also gotten the While...Wend statements to work with simpler "in between" steps, with no issues (e.g., instead of a F/R operation, test for words vs. an empty line, and if words, bold the text, and if empty, quit), and they all worked fine.

But when I put it all together in the "real macro," the same mayhem happens every single time: It compiles just fine, but when I run it, I get a debug error. After I click on debug, it runs the macro on three or four words (correctly), and the debugger will stop at different random spots in the macro, for no rhyme or reason, and with no hint as to what is wrong.

Also, if I perform the macro on the last word in the list, it works properly. And if I place the cursor on an empty line in the document, it also works properly (doing nothing, just as it should).

I'm stumped. I have searched ALL over Google for examples and information about While...Wend loops to see if I could find an explanation. I've also searched my books. But nothing I've tried seems to work. I'm praying for a miracle that someone can look at this and instantly see what I'm doing wrong!

The macro is below, as well as some sample text to copy if anyone feels inclined to test it. Thanks in advance!

Karen


    Sub FindAndHighlightList()

        Dim FRText As String
        Dim iCount As Integer

    Application.ScreenUpdating = False


    'Begin with cursor on first word in "find list."
    'Repeat the following action until no more words are found in the list
    'iCount is precautionary measure in case macro gets hung in the loop

    While Not Selection.Characters(1) = Chr(13) And iCount < 1000
                iCount = iCount + 1

    'Select the current line of text, minus the paragraph mark; store it as FRText; then delete it

        Selection.HomeKey Unit:=wdLine
        Selection.EndKey Unit:=wdLine, Extend:=wdExtend
        Selection.MoveLeft Unit:=wdCharacter, Count:=1, Extend:=wdExtend

        FRText = Selection.Text

        Selection.EndKey Unit:=wdLine, Extend:=wdExtend
        Selection.Delete Unit:=wdCharacter, Count:=1

    'Create a temporary bookmark

        With ActiveDocument.Bookmarks
            .Add Range:=Selection.Range, Name:="TempX"
            .DefaultSorting = wdSortByName
            .ShowHidden = True
        End With

    'Find the selected text and replace add yellow highlight

        Selection.Find.ClearFormatting
        Selection.Find.Replacement.ClearFormatting
        Options.DefaultHighlightColorIndex = wdYellow
        Selection.Find.Replacement.Highlight = True
        With Selection.Find
            .Text = FRText
            .Replacement.Text = FRText
            .Forward = True
            .Wrap = wdFindContinue
            .Format = True
            .MatchCase = True
            .MatchWholeWord = False
            .MatchWildcards = False
            .MatchSoundsLike = False
            .MatchAllWordForms = False
        End With
        Selection.Find.Execute Replace:=wdReplaceAll

    'Go to TempX bookmark location

        Selection.GoTo What:=wdGoToBookmark, Name:="TempX"

    Wend

    If Selection.Characters(1) = Chr(13) Then Exit Sub

        Application.ScreenUpdating = True

    End Sub

SAMPLE TEXT FOR TESTING MACRO

Here is an example of a paragraph with some words I want to highlight. And here are some more words. And I wish I could get this macro to work. I'm losing my mind and my religion trying to fix it! Here is an example of a paragraph with some words I want to highlight.

And here are some more words. And I wish I could get this macro to work. I'm losing my mind and my religion trying to fix it! Here is an example of a paragraph with some words I want to highlight. And here are some more words. And I wish I could get this macro to work. I'm losing my mind and my religion trying to fix it!

Here
example
paragraph
words
macro

1
It sounds like you might be unaware that using Find, when the entire current selection is the exact text you are looking for, will look ahead to the next occurrence, even if that is earlier in the document. - See stackoverflow.com/a/44105332/5757159ThunderFrame
Hi ThunderFrame - Thanks for your response. I'm not quite sure I understand what you're saying though. I do know that, for example, if I manually select some text and then invoke Find/Replace or Find, it will only search within that text, and it will search downward rather than from the beginning of the document. GRRR -- keep hitting Enter! Anyway, that is why I set up the beginning of the macro the way I did - it selects the line of text, stores it as a variable, then deletes is - effectively deselecting it. Then it invokes F/R unencumbered. Does not that make a difference?HappyNanaMO
ThunderFrame, if you're thinking that could be a problem, maybe it wouldn't hurt to add a statement to go to the top of the document to start the search. I'll give that a try.HappyNanaMO
HOLY FREAKING COW!!!!!! I had to pick myself up off the floor just now. I cannot believe my eyes. After two LONNGGG days of screaming and cursing and sighing, I'm in disbelief that the fix was this simple! THANK YOU, THANK YOU, THANK YOU, ThunderFrame. You are a hero. And thank you for the super-quick timeliness of your response as well. You have saved the day ... and my sanity! :):):)HappyNanaMO
I don't see any other voting options on this post other than tagging your comment as useful. Nor do I see any way to mark it as solved. What am I missing?HappyNanaMO

1 Answers

1
votes

ThunderFrame responded to my question with the following response:

It sounds like you might be unaware that using Find, when the entire current selection is the exact text you are looking for, will look ahead to the next occurrence, even if that is earlier in the document

And it worked!

I'm posting his answer here because I did not see any options for upvoting his comment and marking the question as solved. So hopefully, this gives me the option to do that.

Thanks so much, ThunderFrame!