+ Reply to Thread
Results 1 to 10 of 10

Replace merge fields with conditional formula including field creating recursive loop

  1. #1
    Registered User
    Join Date
    02-27-2020
    Location
    St. Louis, Missouri
    MS-Off Ver
    Office 365
    Posts
    4

    Replace merge fields with conditional formula including field creating recursive loop

    I'm trying to loop through a word document containing merge fields and replace it with a conditional formula that includes the merge field I'm replacing. It works if I don't use the initial merge field in the formula but I need to use it and when I do it creates a recursive loop. Could anyone offer some advice on how to stop the looping after the first assessment of the fields in the document?

    Basically I want it to go from this to this:

    2020-06-22_14-53-07.jpg

    This is the macro I'm using that causes the recursive loop. Any help would be greatly appreciated! (I can't get the actual text to load in because it's getting blocked by the Sucuri system)

    2020-06-22_15-36-48.jpg

  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,726

    Re: Replace merge fields with conditional formula including field creating recursive loop

    Try:
    Please Login or Register  to view this content.
    Last edited by macropod; 06-29-2020 at 06:50 PM.
    Cheers,
    Paul Edstein
    [Fmr MS MVP - Word]

  3. #3
    Registered User
    Join Date
    02-27-2020
    Location
    St. Louis, Missouri
    MS-Off Ver
    Office 365
    Posts
    4

    Re: Replace merge fields with conditional formula including field creating recursive loop

    Quote Originally Posted by macropod View Post
    Try:
    Please Login or Register  to view this content.
    Thank you! I tried that and got this error:

    Attachment 683882

    I thought maybe you meant for that "-" to be an "=" so I tried that and it ran but nothing happened. Any thoughts?

  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,726

    Re: Replace merge fields with conditional formula including field creating recursive loop

    Your 'attachment' is an invalid link...

  5. #5
    Registered User
    Join Date
    02-27-2020
    Location
    St. Louis, Missouri
    MS-Off Ver
    Office 365
    Posts
    4

    Re: Replace merge fields with conditional formula including field creating recursive loop

    Quote Originally Posted by macropod View Post
    Your 'attachment' is an invalid link...
    It's giving a Compile Error: Invalid use of Property on the line
    Please Login or Register  to view this content.

  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,726

    Re: Replace merge fields with conditional formula including field creating recursive loop

    That line should read:
    .End = .End - 1
    Fixed.

  7. #7
    Registered User
    Join Date
    02-27-2020
    Location
    St. Louis, Missouri
    MS-Off Ver
    Office 365
    Posts
    4

    Re: Replace merge fields with conditional formula including field creating recursive loop

    Quote Originally Posted by macropod View Post
    That line should read:
    .End = .End - 1
    Fixed.
    So, that worked on the first set but it wiped out the rest of the 30+ page document including the second FirmAddress merge field.

    Before:

    Before.jpg

    After:

    After.jpg

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

    Re: Replace merge fields with conditional formula including field creating recursive loop

    That suggests you might have paragraphs containing only the {MERGEFIELD FirmAddress} field. I've modified the code slightly to cope with that better.

  9. #9
    Registered User
    Join Date
    06-12-2020
    Location
    Steamboat Springs, Colorado
    MS-Off Ver
    2016
    Posts
    1

    Re: Replace merge fields with conditional formula including field creating recursive loop

    Hi macropod, thanks for your help with this. I've been trying to get your code script working in production. I can get this script to operate and merge properly on a local test, but I'm running into an issue when running this server side.

    i.imgur.com/8NMafO2.png

    On the left is the formula added manually using Word drop-down functions and on the right is what was generated by using this macro (below - a slight tweak on the version you posted in an attempt to get it to work properly).

    Please Login or Register  to view this content.
    When using the manually entered fields, I get the proper merge data, but when using this script I just get the names of the merge fields.

    i.imgur.com/nFfyO1u.png

    Do you have any idea what might be going wrong? I had the same results in using the original version of the script you posted as well. Any help would be greatly appreciated.
    Apologies for the odd formatting on the image/code links, my account is too new to post images properly.

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

    Re: Replace merge fields with conditional formula including field creating recursive loop

    That suggests whatever you're using on the server side is broken or doesn't the creation of fields in Word.

+ 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. Creating a new pivot field (sum of two fields)
    By bdav1216 in forum Excel Charting & Pivots
    Replies: 1
    Last Post: 10-22-2017, 01:18 PM
  2. Group Fields in Mail merge based on common field
    By Sinon05 in forum Word Formatting & General
    Replies: 2
    Last Post: 03-04-2016, 01:46 AM
  3. [SOLVED] Need help with creating a formula to auto pop 3 fields based on selection in one field.
    By chiefgb in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 12-02-2014, 05:25 PM
  4. Replies: 1
    Last Post: 08-28-2012, 12:32 AM
  5. Find & replace recursive
    By mmer2 in forum Excel General
    Replies: 7
    Last Post: 07-18-2012, 08:41 AM
  6. Calling a recursive Sub in a 'for' loop in another Sub
    By excelworker_1 in forum Excel Programming / VBA / Macros
    Replies: 17
    Last Post: 06-15-2012, 10:57 AM
  7. [SOLVED] Recursive Loop -- How to Stop It?
    By LarryP in forum Excel Programming / VBA / Macros
    Replies: 6
    Last Post: 06-28-2006, 03:50 PM

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