Results 1 to 9 of 9
  1. #1
    Star Lounger
    Join Date
    Jul 2008
    Posts
    68
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Need help to make a macro stop looping (VBA MSWord

    This is my first post. I need to make the macro shown below stop searching and processing (looping) when the macro can no longer find text to process.
    The text to be searched is buried in other text. However, to make this simple and more workable, I have posted sample text below.
    Here is sample text to be searched and processed by the macro.
    <code>
    </FONT><U><FONT FACE="Arial" SIZE=2 COLOR="#007f00">Gen_1:16</U></FONT><FONT FACE="Arial" SIZE=2>
    </FONT><U><FONT FACE="Arial" SIZE=2 COLOR="#007f00">Gen_10:6</U></FONT><FONT FACE="Arial" SIZE=2>
    </FONT><U><FONT FACE="Arial" SIZE=2 COLOR="#007f00">Gen_45:20</U></FONT><FONT FACE="Arial" SIZE=2>
    </FONT><U><FONT FACE="Arial" SIZE=2 COLOR="#007f00">Exo_12:22</U></FONT><FONT FACE="Arial" SIZE=2>
    </FONT><U><FONT FACE="Arial" SIZE=2 COLOR="#007f00">Exo_45:16</U></FONT><FONT FACE="Arial" SIZE=2>
    </FONT><U><FONT FACE="Arial" SIZE=2 COLOR="#007f00">Lev_4:10</U></FONT><FONT FACE="Arial" SIZE=2>
    </FONT><U><FONT FACE="Arial" SIZE=2 COLOR="#007f00">Lev_29:12</U></FONT><FONT FACE="Arial" SIZE=2>

    </FONT><U><FONT FACE="Arial" SIZE=2 COLOR="#007f00">Num_14:16</U></FONT><FONT FACE="Arial" SIZE=2>

    </FONT><U><FONT FACE="Arial" SIZE=2 COLOR="#007f00">Deu_7:20</U></FONT><FONT FACE="Arial" SIZE=2>
    </code>


    Here is the macro. The purpose of the macro is to search text like the above, cycle through the booknames, and exit when the bookname list is finished. There will be 66 booknames total when the macro finally works.

    Thank you in advance for your replies.

    <code>
    Sub BuildHyperlinkPartOne()
    '
    '
    Dim Bookname
    Do While N < 66
    N = N + 1
    If N = 1 Then Bookname = "Gen_"
    If N = 2 Then Bookname = "Exo_"
    If N = 3 Then Bookname = "Lev_"
    'other booknames go here

    Selection.Find.ClearFormatting
    With Selection.Find
    .Text = Bookname
    .Replacement.Text = ""
    .Forward = True
    .Wrap = wdFindContinue
    .Format = False
    .MatchCase = False
    .MatchWholeWord = False
    .MatchWildcards = False
    .MatchSoundsLike = False
    .MatchAllWordForms = False
    End With
    Selection.Find.Execute

    Selection.Find.ClearFormatting
    With Selection.Find
    .Text = "</U></FONT>"
    .Replacement.Text = ""
    .Forward = True
    .Wrap = wdFindContinue
    .Format = False
    .MatchCase = False
    .MatchWholeWord = False
    .MatchWildcards = False
    .MatchSoundsLike = False
    .MatchAllWordForms = False
    End With
    Selection.Find.Execute

    Selection.MoveLeft Unit:=wdCharacter, Count:=1
    Selection.TypeText Text:="""></a>"
    Selection.Find.ClearFormatting
    With Selection.Find
    .Text = """>"
    .Replacement.Text = ""
    .Forward = False
    .Wrap = wdFindStop
    .Format = False
    .MatchCase = False
    .MatchWholeWord = False
    .MatchWildcards = False
    .MatchSoundsLike = False
    .MatchAllWordForms = False
    End With
    Selection.Find.Execute
    Selection.MoveLeft Unit:=wdCharacter, Count:=1
    Selection.TypeParagraph
    If Not Selection.Find.Execute Then Exit Do
    Selection.Find.ClearFormatting
    With Selection.Find
    .Text = Bookname
    .Replacement.Text = ""
    .Forward = False
    .Wrap = wdFindStop
    .Format = False
    .MatchCase = False
    .MatchWholeWord = False
    .MatchWildcards = False
    .MatchSoundsLike = False
    .MatchAllWordForms = False
    End With
    Selection.Find.Execute
    Selection.EndKey Unit:=wdLine, Extend:=wdExtend
    Selection.MoveLeft Unit:=wdCharacter, Count:=1, Extend:=wdExtend
    Selection.Copy
    Selection.MoveRight Unit:=wdCharacter, Count:=1
    Selection.Delete Unit:=wdCharacter, Count:=1
    Selection.MoveRight Unit:=wdCharacter, Count:=2
    Selection.PasteAndFormat (wdPasteDefault)
    Selection.Find.ClearFormatting
    With Selection.Find
    .Text = "</U></FONT>"
    .Replacement.Text = ""
    .Forward = True
    .Wrap = wdFindStop
    .Format = False
    .MatchCase = False
    .MatchWholeWord = False
    .MatchWildcards = False
    .MatchSoundsLike = False
    .MatchAllWordForms = False
    End With
    Selection.Find.Execute

    Selection.MoveRight Unit:=wdCharacter, Count:=1
    Loop
    End Sub
    </code>

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Need help to make a macro stop looping (VBA MSWord

    Welcome to Woody's Lounge!

    I didn't study your code in detail, but the Execute method of the Find object actually returns a value: True if the search text was found, False if not. So you can do something like this:

    If Selection.Find.Execute = True Then
    ' action if search text found
    Else
    ' action if search text not found
    End If

  3. #3
    Star Lounger
    Join Date
    Jul 2008
    Posts
    68
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Need help to make a macro stop looping (VBA MS

    Thanks Hans, for the reply. I will use your suggestion to see if I can get the macro to stop looping. Thanks again.

  4. #4
    Star Lounger
    Join Date
    Jul 2008
    Posts
    68
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Need help to make a macro stop looping (VBA MS

    For archival purposes, this macro has any number is issues with it. The looping sequence was supposed to iterate through the book names but the way the macro is written, occurences of any booknumber would be searched for and processed only one time. I believe a macro within a macro would resolve that issue. in other words, It does not search for all the bookname=1's and then search for all the bookname=2's then search for all the bookname=3's.

    Then, at the end of the page to be searched, instead of stopping the search going down, the macro keeps on searching by starting to search up the page even though the first block of code says .Forward = True.

  5. #5
    Super Moderator jscher2000's Avatar
    Join Date
    Feb 2001
    Location
    Silicon Valley, USA
    Posts
    23,112
    Thanks
    5
    Thanked 93 Times in 89 Posts

    Re: Need help to make a macro stop looping (VBA MS

    The "Forward" property is interpreted only to refer to the direction of the search in the document, down or up. It is the "Wrap" property that determines whether to stop at the end of the document. If using wdFindStop, it's helpful to make sure you are starting your search for a known correct location. <img src=/S/smile.gif border=0 alt=smile width=15 height=15>

    Added: If you are thinking of posting that code on a web page, consider rewriting it using a more modern approach. You will radically reduce the size of the document and make it easier to process in the future.

    Also: Since all 66 book names have the same replacement, why not just do one global replace in this first step?

  6. #6
    Star Lounger
    Join Date
    Jul 2008
    Posts
    68
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Need help to make a macro stop looping (VBA MS

    Thank you for the response. All 66 book names are different so I cannot do a replaceall.

    The sample html is buried in other text like an encyclopedia and that text goes into a MySql/php database.

    I understand about the "Wrap" property. The problem is what do I do when the direction of the cursor controlled by the macro goes right and left of up and down. I have another entirely different block of code with a need to stop at the bottom of the document. For clarity and to help others I will start a new thread.

  7. #7
    Super Moderator
    Join Date
    May 2002
    Location
    Canberra, Australian Capital Territory, Australia
    Posts
    5,055
    Thanks
    2
    Thanked 417 Times in 346 Posts

    Re: Need help to make a macro stop looping (VBA MSWord

    Hi HowdeeDoodee,

    Try the following:
    Sub BuildHyperlinkPartOne()
    Dim Books As String
    Dim Bookname As String
    Dim i As Integer
    Books = "Gen,Exo,Lev"
    For i = 0 To UBound(Split(Books, ","))
    Bookname = Split(Books, ",")(i)
    With ActiveDocument.Content.Find
    .ClearFormatting
    .Text = Bookname & "?_([0-9]{1,3})[0-9]{1,3})"
    .Replacement.Text = "^&""^62""^62^&^60/a^62"
    .Forward = True
    .Wrap = wdFindContinue
    .Format = False
    .MatchCase = False
    .MatchWholeWord = False
    .MatchAllWordForms = False
    .MatchSoundsLike = False
    .MatchWildcards = True
    .Execute Replace:=wdReplaceAll
    End With
    Next
    End Sub

    Just add the rest of your book names.
    Cheers,

    Paul Edstein
    [MS MVP - Word]

  8. #8
    Star Lounger
    Join Date
    Jul 2008
    Posts
    68
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Need help to make a macro stop looping (VBA MS

    Excellent. Macropod, you know your stuff!!! Wonder what the scribes with quill and ink would think about what we do? Thank you very much. You time and work is appreciated.

  9. #9
    Super Moderator
    Join Date
    May 2002
    Location
    Canberra, Australian Capital Territory, Australia
    Posts
    5,055
    Thanks
    2
    Thanked 417 Times in 346 Posts

    Re: Need help to make a macro stop looping (VBA MS

    Hi HowdeeDoodee,

    Your task, if you accept it, is to decipher the code ...
    Cheers,

    Paul Edstein
    [MS MVP - Word]

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •