+ Reply to Thread
Results 1 to 7 of 7

Making a macro loop

Hybrid View

  1. #1
    Registered User
    Join Date
    02-21-2024
    Location
    Middx UK
    MS-Off Ver
    2003
    Posts
    68

    Making a macro loop

    I have a macro which identifies a place using a key text ('BX') in the document , and the next one below it. It establishes two placeholders ('First' and 'Next') , and then deletes the text between.

    The only problem is it only works for the first instance of the key text , but then stops.

    Can someone suggest a way to modify it so it runs through to the bottom of the document and finds all examples , and not just the first?

    Sub Macro8()
    '
    ' Macro8 Macro
    '
        Selection.Find.ClearFormatting
        With Selection.Find
            .Text = "BX"
            .Replacement.Text = ""
            .Forward = True
            .Wrap = wdFindContinue
            .Format = False
            .MatchCase = False
            .MatchWholeWord = False
            .MatchAllWordForms = False
            .MatchSoundsLike = False
            .MatchWildcards = True
        End With
        Selection.Find.Execute
        Selection.EndKey Unit:=wdLine
        Selection.TypeText Text:="First"
        Selection.Find.ClearFormatting
        With Selection.Find
            .Text = "BX"
            .Replacement.Text = ""
            .Forward = True
            .Wrap = wdFindContinue
            .Format = False
            .MatchCase = False
            .MatchWholeWord = False
            .MatchAllWordForms = False
            .MatchSoundsLike = False
            .MatchWildcards = True
        End With
        Selection.Find.Execute
        Selection.HomeKey Unit:=wdLine
        Selection.MoveUp Unit:=wdLine, Count:=1
        Selection.TypeText Text:="Next"
        Selection.MoveUp Unit:=wdScreen, Count:=8
        Selection.Find.ClearFormatting
        Selection.Find.Replacement.ClearFormatting
        With Selection.Find
            .Text = "First*Next"
            .Replacement.Text = "^p^p"
            .Forward = True
            .Wrap = wdFindContinue
            .Format = False
            .MatchCase = False
            .MatchWholeWord = False
            .MatchAllWordForms = False
            .MatchSoundsLike = False
            .MatchWildcards = True
        End With
        Selection.Find.Execute Replace:=wdReplaceAll
    End Sub

    Grateful for any advice.

    Last edited by CaMeSuffit; 01-09-2025 at 11:55 PM.
    Currently using Access , Word and Excel 2003

  2. #2
    Forum Expert macropod's Avatar
    Join Date
    12-22-2011
    Location
    Canberra, Australia
    MS-Off Ver
    Word, Excel & Powerpoint 2003 & 2010
    Posts
    3,838

    Re: Making a macro loop

    It's not apparent what you mean about the 'BX', since there's nothing like that in your 'Before' document. In any event, if the aim is to delete the two 'BX' markers and everything in between, that could be done with a wildcard Find/Replace, where:
    Find = BX*BX
    Replace = nothing
    Cheers,
    Paul Edstein
    [Fmr MS MVP - Word]

  3. #3
    Registered User
    Join Date
    02-21-2024
    Location
    Middx UK
    MS-Off Ver
    2003
    Posts
    68

    Re: Making a macro loop

    Hi macropod - thanks for getting back. Sorry if the aim here is not clear.

    The lines containing 'BX' are catalogue numbers and need to be maintained in their entirety , as well as the Item Description that goes with each.

    EG

    BEATLES - ABBEY ROAD
    235698BX10


    With my macro I'm trying to delete extraneous text between.

    So , for example

    ABBA - KNOWING ME KNOWING YOU
    148875BX103

    Text to delete1

    CA VA CA VA - WHERE`S ROMEO
    148653BX21

    Text to delete2

    MATCHBOX - MIDNITE DYNAMOS
    146020BX150

    Text to delete3

    ROXETTE - DRESSED FOR SUCCESS
    103922BX130

    Text to delete4

    would become

    ABBA - KNOWING ME KNOWING YOU PRONG
    148875BX103

    CA VA CA VA - WHERE`S ROMEO
    148653BX21

    MATCHBOX - MIDNITE DYNAMOS
    146020BX150

    ROXETTE - DRESSED FOR SUCCESS
    103922BX130


    The macro I posted deals with the first instance successfully , but then stops there. I was wanting it to go through and remove each example of unwanted text.

    In essence I'm doing this in the macro ;

    1. Find the first instance of 'BX' in the catalogue number. Move cursor to the end and enter the word 'First'

    2. Find the next instance of 'BX' and move the cursor to the beginning of the line and up one line. Enter the word 'Next'. This puts 'First' at the end of the first Catalogue Number and 'Next' at the beginning of the next Item Description , ready to delete whatever text is between.

    3. Replace everything between 'First and 'Next' with two paragraph returns.

    I'd like at that point for the macro to continue to the next instance and delete superfluous text until only the Catalogue Numbers and Item Descriptions remain.

    I wondered if placing some 'Stop' text at the bottom of the document would cause the macro to finish at that point. Otherwise , it might not know when to stop as each list varies in length.

    I updated the sample files , so hopefully they will be more helpful.


    Attached Files Attached Files
    Last edited by CaMeSuffit; 01-10-2025 at 12:46 AM.

  4. #4
    Forum Expert macropod's Avatar
    Join Date
    12-22-2011
    Location
    Canberra, Australia
    MS-Off Ver
    Word, Excel & Powerpoint 2003 & 2010
    Posts
    3,838

    Re: Making a macro loop

    Inn which case you could use a macro like:
    Sub Demo()
    Application.ScreenUpdating = False
    With ActiveDocument.Range
      With .Find
        .ClearFormatting
        .Replacement.ClearFormatting
        .Text = "BX*BX"
        .Replacement.Text = ""
        .Forward = True
        .Wrap = wdFindStop
        .Format = False
        .MatchWildcards = True
      End With
      Do While .Find.Execute
        .Start = .Paragraphs.First.Range.End
        .End = .Paragraphs.Last.Previous.Range.Start
        .Delete
        .Collapse wdCollapseEnd
      Loop
      With .Find
        .Text = "BX"
      End With
      Do While .Find.Execute
        .Start = .Paragraphs.First.Range.End
        .End = ActiveDocument.Range.End
        .Delete
        .Collapse wdCollapseEnd
      Loop
    End With
    Application.ScreenUpdating = True
    End Sub
    Last edited by macropod; 01-10-2025 at 01:56 AM.

  5. #5
    Registered User
    Join Date
    02-21-2024
    Location
    Middx UK
    MS-Off Ver
    2003
    Posts
    68

    Re: Making a macro loop

    Thanks macropod. I'll give it a go.

  6. #6
    Forum Expert macropod's Avatar
    Join Date
    12-22-2011
    Location
    Canberra, Australia
    MS-Off Ver
    Word, Excel & Powerpoint 2003 & 2010
    Posts
    3,838

    Re: Making a macro loop

    Even faster - with no loops:
    Sub Demo()
    Application.ScreenUpdating = False
    With ActiveDocument.Range
      With .Find
        .ClearFormatting
        .Replacement.ClearFormatting
        .Wrap = wdFindContinue
        .MatchWildcards = True
        .Format = False
        .Forward = True
        .Text = "[ ]@^13"
        .Replacement.Text = "^p"
        .Execute Replace:=wdReplaceAll
        .Text = "(X[0-9]@^13{2})*([!^13]@^13[!^13][0-9]@B)"
        .Replacement.Text = "\1\2"
        .Execute Replace:=wdReplaceAll
        .Wrap = wdFindStop
        .Forward = False
        .Text = "(BX[0-9]@)^13{2}[!^13]@^13"
        .Replacement.Text = "\1"
        .Execute Replace:=wdReplaceOne
      End With
    End With
    Application.ScreenUpdating = True
    End Sub

  7. #7
    Registered User
    Join Date
    02-21-2024
    Location
    Middx UK
    MS-Off Ver
    2003
    Posts
    68

    Re: Making a macro loop

    Brilliantly done , macropod.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. [SOLVED] Making Code Loop
    By Bill1234 in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 11-22-2023, 08:58 AM
  2. making optionbutton visible or invisible by loop
    By lettersofberk in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 08-13-2013, 12:33 PM
  3. [SOLVED] Making a For loop to loop through a set of cells
    By Smeddlesboy in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 04-06-2012, 08:52 PM
  4. Making a simple loop faster
    By gummi in forum Excel Programming / VBA / Macros
    Replies: 7
    Last Post: 08-23-2008, 07:07 PM
  5. [SOLVED] Making macros loop
    By McKCollins in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 07-10-2006, 08:59 AM
  6. Need Help on Making a Loop
    By japorms in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 09-16-2005, 09:05 AM

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1