VBA to replace a repeated single variable with variable from a list

Edit4Fun · #1 08-28-2022, 08:58 AM

Hi. I probably haven't described that very clearly, but what I want to do is this:

I have a list in word that has identical list points with the exception of a single variable (VAR), such as:

This VAR cup goes with this VAR saucer.
This VAR cup goes with this VAR saucer.
This VAR cup goes with this VAR saucer.

and what I want to achieve is

This red cup goes with this red saucer.
This white cup goes with this white saucer.
This blue cup goes with this blue saucer.

So I guess I need a macro that contains a list (array?) of my variables (red, white, blue), and a means of calling the first variable to replace VAR in the first para only; the second to replace VAR in the second para, and so on. (In real life, my list could be a couple of hundred terms long.) If it would be easier if I made a table, and specified changes by row rather than by para, I could do that too. Or even make a 5-col table, with the two VARs in their own columns, which might open up the possibility of having a VAR1 and a VAR2 called from two different lists. (I'm rambling now. Sorry, I'll get me coat.)

Thanks for reading.

macropod · #2 08-28-2022, 03:43 PM

At its simplest:

Code:

Sub Demo()
Application.ScreenUpdating = False
Dim StrRep As String, i As Long
StrRep = "red|white|blue"
With ActiveDocument.Range.Find
  .ClearFormatting
  .Replacement.ClearFormatting
  .Forward = True
  .Wrap = wdFindContinue
  .MatchWildcards = True
  .Text = "<VAR>"
  For i = 0 To UBound(Split(StrRep, "|"))
    .Replacement.Text = Split(StrRep, "|")(i)
    .Execute Replace:=wdReplaceOne
    .Execute Replace:=wdReplaceOne
  Next
End With
Application.ScreenUpdating = True
End Sub

For a similar macro that would contain your replacments in a list in a separate document, see: https://www.msofficeforums.com/132829-post2.html

Edit4Fun · #3 08-28-2022, 05:12 PM

Thankyou @macropod. I'll experiment with those and see if they'll do what I'm hoping (my lists are not as simple as those I posted). I might be back

macropod · #4 08-28-2022, 08:48 PM

For operations requiring greater variation in the F/R expressions, see:
https://www.msofficeforums.com/word-...doc2-copy.html
https://www.msofficeforums.com/word-...d-replace.html
https://www.msofficeforums.com/word-...sing-word.html

maniek · #5 08-29-2022, 02:04 AM

I understand - replace each variable twice.

This works for me:

Sub Demo() 'replace each variable twice
'https://www.msofficeforums.com/word-vba/49582-vba-replace-repeated-single-variable-variable-list.html
Application.ScreenUpdating = False
Dim StrRep As String, i As Long, k As Long: k = 1
StrRep = "red|white|blue" '##########

With ActiveDocument.Range.Find
.ClearFormatting
.Replacement.ClearFormatting
.Forward = True
'.Wrap = wdFindStop
.Wrap = wdFindContinue
.MatchWildcards = True
.Text = "<VAR>" '##############
.Execute

For i = 0 To UBound(Split(StrRep, "|")) '0
.Replacement.Text = Split(StrRep, "|")(i)

Do While .Found
.Execute Replace:=wdReplaceOne, Forward:=True 'we need to replace 2 consecutive occurances!
k = k + 1
If k > 2 Then 'only twice
Exit Do
End If
Loop
k = 1
Next

End With

Edit4Fun · #6 08-29-2022, 04:23 AM

Thank you, both

For context, I am looking to semi-automate the titling, description and tag-writing process for a series of related product designs. Let's say mugs with different cat breeds on them as an example. So an entry might be:

Cute [BREED] mug
White ceramic mug with a [BREED] design
[BREED] cat, [BREED] gift, cat lover gift, Christmas gift

and that format would be repeated for each breed I've designed for. So far, I've been copy-pasting the sample entry to create a list with as many entries as I have breeds, copying the breed name from a breed-name list (in a separate document), highlighting the first entry, FR [BREED] with ^c, then rinse and repeat to the end of the list. Since the entries all have the same format, the number of replacements would be constant for each search (4 in this case), and easily varied if necessary for a different design next time.

macropod · #7 08-29-2022, 07:11 AM

If you need to replace the same variable twice, simply use:

Code:

    .Execute Replace:=wdReplaceOne

twice...

Edit4Fun · #8 08-29-2022, 01:59 PM

Quote:

Originally Posted by macropod

If you need to replace the same variable twice, simply use:

Code:

    .Execute Replace:=wdReplaceOne

twice...

Thanks again, that's helpful for short lists. The list I did had 24 replacements per set. This will be an ongoing procedure. I guess the solution @maniek proposed is more elegant than making 23 (or however many) copy-and-pastes?

macropod · #9 08-29-2022, 02:53 PM

No, that would be less efficient than, for example:

Code:

Sub Demo()
Application.ScreenUpdating = False
Dim StrRep As String, i As Long, j As Long
StrRep = "red|white|blue"
With ActiveDocument.Range.Find
  .ClearFormatting
  .Replacement.ClearFormatting
  .Forward = True
  .Wrap = wdFindContinue
  .MatchWildcards = True
  .Text = "<VAR>"
  For i = 0 To UBound(Split(StrRep, "|"))
    .Replacement.Text = Split(StrRep, "|")(i)
    For j = 1 To 24
      .Execute Replace:=wdReplaceOne
    Next
  Next
End With
Application.ScreenUpdating = True
End Sub

Edit4Fun · #10 08-29-2022, 03:52 PM

Thanks so much; this is going to be truly helpful to me. Since you're on a roll, could I (read "you") modify that to include a VAR1 and a VAR2 in the same listing?

macropod · #11 08-30-2022, 08:10 AM

You will need to explain what you mean by that - including examples.

Edit4Fun · #12 08-30-2022, 10:44 AM

Quote:

Originally Posted by macropod

You will need to explain what you mean by that - including examples.

OK, thanks, I'll try to be clearer. Say we offer red, white and blue mugs in 3 different patterns, dot, star and check.

Thus VAR1 = red | white | blue
VAR2 = dot | star | check

Output will be 9 lines: (3xVAR1) mug with a (3xVAR2) pattern. Adding an n-variation VAR3 would produce 3 x 3 x n lines etc.

Before:
VAR1 mug with a VAR2 pattern
VAR1 mug with a VAR2 pattern
VAR1 mug with a VAR2 pattern
VAR1 mug with a VAR2 pattern
VAR1 mug with a VAR2 pattern
VAR1 mug with a VAR2 pattern
VAR1 mug with a VAR2 pattern
VAR1 mug with a VAR2 pattern
VAR1 mug with a VAR2 pattern

=========================
VAR1 can have the value red, white or blue
VAR2 can have the value dot, star or check, so
=========================

After:
Red mug with a dot pattern
Red mug with a star pattern
Red mug with a check pattern
White mug with a dot pattern
White mug with a star pattern
White mug with a check pattern
Blue mug with a dot pattern
Blue mug with a star pattern
Blue mug with a check pattern

macropod · #13 08-31-2022, 05:49 AM

For example:

Code:

Sub DemoB()
Application.ScreenUpdating = False
Dim StrVAR1 As String, StrVAR2 As String, i As Long, j As Long
StrVAR1 = "red|white|blue"
StrVAR2 = "dot|star|check"
With ActiveDocument.Range.Find
  .ClearFormatting
  .Replacement.ClearFormatting
  .Forward = True
  .Wrap = wdFindContinue
  .MatchWildcards = True
  For i = 0 To UBound(Split(StrVAR1, "|"))
    For j = 0 To UBound(Split(StrVAR2, "|"))
      .Text = "<VAR1>"
      .Replacement.Text = Split(StrVAR1, "|")(i)
      .Execute Replace:=wdReplaceOne
      .Text = "<VAR2>"
      .Replacement.Text = Split(StrVAR2, "|")(j)
      .Execute Replace:=wdReplaceOne
   Next
  Next
End With
Application.ScreenUpdating = True
End Sub

Edit4Fun · #14 08-31-2022, 10:56 AM

And we have a winner! Thanks very much, Paul.

Similar Threads
Thread	Thread Starter	Forum	Replies	Last Post
I want to create a bar chart of multiple variable. Then I need to draw trend lin of those variable	shimulsiddiquee	Excel	1	05-16-2017 07:39 AM
ord vba replace the variable text with variable images to make offer products with images	tanzinim	Word VBA	4	12-30-2015 01:40 PM
Run Time Error '91': Object variable or With block variable not set using Catalogue Mailmerge	Berryblue	Mail Merge	1	11-13-2014 05:36 PM
Run-time error 91 object variable or with block variable not set	JUST ME	Word VBA	4	03-25-2014 06:56 AM
Run-time error '91': Object variable or With block variable not set	tinfanide	Excel Programming	2	06-10-2012 10:17 AM