+ Reply to Thread
Results 1 to 13 of 13

Problem - I'm having to run a macro multiple times to catch all instances

  1. #1
    Registered User
    Join Date
    01-25-2012
    Location
    Virginia
    MS-Off Ver
    Excel 2007
    Posts
    17

    Problem - I'm having to run a macro multiple times to catch all instances

    I'm looping over a document, finding all the hyperlinks, checking to see if they contain the word "Figure" or "Table" and if they do, removing the hyperlink and changing the text to black. This works, but it only catches some of the hyperlinks on the first run. Then it catches more on the next run, more on the next run, etc. I have to run it 5-6 times to catch all instances.

    How can I fix this to catch all instances on the first run?

    Please Login or Register  to view this content.

  2. #2
    Forum Expert Logit's Avatar
    Join Date
    12-23-2012
    Location
    North Carolina
    MS-Off Ver
    Excel 2019 Professional Plus - 2007 Enterprise
    Posts
    6,550

    Re: Problem - I'm having to run a macro multiple times to catch all instances

    Untested here !

    Will this work ? Only this macro ... do not use the others in your post.

    Please Login or Register  to view this content.

  3. #3
    Registered User
    Join Date
    01-25-2012
    Location
    Virginia
    MS-Off Ver
    Excel 2007
    Posts
    17

    Re: Problem - I'm having to run a macro multiple times to catch all instances

    In your example, where does link_set get passed in from?

  4. #4
    Registered User
    Join Date
    01-25-2012
    Location
    Virginia
    MS-Off Ver
    Excel 2007
    Posts
    17

    Re: Problem - I'm having to run a macro multiple times to catch all instances

    I made the macro much simpler but it's still doing the same thing. I need to run it several times to catch all instances.

    Please Login or Register  to view this content.

  5. #5
    Registered User
    Join Date
    01-25-2012
    Location
    Virginia
    MS-Off Ver
    Excel 2007
    Posts
    17

    Re: Problem - I'm having to run a macro multiple times to catch all instances

    Anyone have an answer for why this is happening? I ended up band-aiding a fix for this to get it working. I set a variable at the start of my function equal to ActiveDocument.Hyperlinks.Count and then I check that value again at the end of the function. If the two values are not equal, it means some links were stripped off. So I call the function again and repeat everything. I keep calling the function over and over until finally the starting link count equals the ending link count - this means no links were stripped off during this last loop and so we must be done.

    Usually it has to call itself about 6-8 times until it's deleted all the links. This isn't how it should work. Luckily the script only takes a few seconds, but I still really wish I understood why it's happening.

  6. #6
    Forum Expert
    Join Date
    08-17-2007
    Location
    Poland
    Posts
    1,275

    Re: Problem - I'm having to run a macro multiple times to catch all instances

    Attach a sample workbook, it will be easier to find the cause of the problem.

    Artik

  7. #7
    Registered User
    Join Date
    01-25-2012
    Location
    Virginia
    MS-Off Ver
    Excel 2007
    Posts
    17

    Re: Problem - I'm having to run a macro multiple times to catch all instances

    Quote Originally Posted by Artik View Post
    Attach a sample workbook, it will be easier to find the cause of the problem.

    Artik
    Good idea - here it is: https://www.excelforum.com/attachmen...1&d=1625585279

    Run the macro called "RUN_THIS_ONE()"
    I have this example set to remove links containing the words: park, baby, elephant, asia,species,afr,cavity
    On my computer it has to re-run the function 3 times before it is able to strip away all 155 of the links with those words.
    Last edited by kandiyohiv; 07-06-2021 at 11:34 AM.

  8. #8
    Forum Expert
    Join Date
    08-17-2007
    Location
    Poland
    Posts
    1,275

    Re: Problem - I'm having to run a macro multiple times to catch all instances

    This link is not working! Read and apply the suggestion shown in the yellow banner at the beginning of the thread.

    Artik

  9. #9
    Registered User
    Join Date
    01-25-2012
    Location
    Virginia
    MS-Off Ver
    Excel 2007
    Posts
    17

    Re: Problem - I'm having to run a macro multiple times to catch all instances

    Quote Originally Posted by Artik View Post
    This link is not working! Read and apply the suggestion shown in the yellow banner at the beginning of the thread.

    Artik
    I thought I did... Either I'm doing something wrong or something isn't working. I click on "manage attachments" and then upload the file from my computer. Initially it shows that it added (which is how I grabbed the link to the file). I even tested downloading it to make sure it worked (it did) but then if I go back to "manage attachments" later it shows I have no attachments. I tried from Edge, Chrome, IE and even tried from my home computer (instead of work computer) to make sure it isn't being blocked by my work's security team. Same results in all scenarios.

    elephants.png


    2021-07-06_13-25-14.png
    Last edited by kandiyohiv; 07-06-2021 at 01:26 PM.

  10. #10
    Forum Expert
    Join Date
    08-17-2007
    Location
    Poland
    Posts
    1,275

    Re: Problem - I'm having to run a macro multiple times to catch all instances

    Try:
    1. Click [Choose file] button
    2. After the file selection window is closed, click [Upload] button
    3. Click [Close this window] button
    4. And finally, click [Submit Reply].

    Artik

  11. #11
    Registered User
    Join Date
    01-25-2012
    Location
    Virginia
    MS-Off Ver
    Excel 2007
    Posts
    17

    Re: Problem - I'm having to run a macro multiple times to catch all instances

    Trying again

  12. #12
    Forum Expert
    Join Date
    08-17-2007
    Location
    Poland
    Posts
    1,275

    Re: Problem - I'm having to run a macro multiple times to catch all instances

    Hmm, and I thought all the time we were going to talk about Excel, and here is such a surprise. OK, if it has to be done, it will be done.
    Watching the macro work, I noticed that it removes every other hyperlink with one run. This reminds me of the problem of deleting rows in Excel. If we use an ascending loop, some rows may be omitted, therefore a descending loop is used.
    And likewise here. This loop should be used. Only change at the beginning of one procedure:
    Please Login or Register  to view this content.
    Artik

  13. #13
    Registered User
    Join Date
    01-25-2012
    Location
    Virginia
    MS-Off Ver
    Excel 2007
    Posts
    17

    Re: Problem - I'm having to run a macro multiple times to catch all instances

    Thank you so much for your help. Sorry about the slow response, I was on vacation. I have marked this as solved.

+ 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. Replies: 3
    Last Post: 06-24-2021, 10:41 AM
  2. Replies: 10
    Last Post: 11-16-2018, 11:37 AM
  3. [SOLVED] Complex VLOOKUP, multiple instances vertical, return subsequent instances horizontally
    By Miles_2804 in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 08-12-2013, 11:54 AM
  4. [SOLVED] Macro needs to be run many times to catch all occurrences
    By MrMcLee in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 05-30-2013, 10:43 AM
  5. Macro to sum multiple instances
    By stjohnsmythe in forum Excel Programming / VBA / Macros
    Replies: 6
    Last Post: 12-13-2010, 06:53 AM
  6. macro for saving multiple instances of an xls in multiple formats.
    By tomotron in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 10-29-2008, 12:01 AM
  7. [SOLVED] Catch _AfterSave Event & Saved Property Problem ...
    By Joe HM in forum Excel Programming / VBA / Macros
    Replies: 7
    Last Post: 03-17-2005, 08:06 AM

Tags for this Thread

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