Find/Replace conditionally. Branch if bold.

[jason456]

I'm new to VB and mail merging, so my terminology may be a little off. Thanks for understanding.

So, I have a word doc that I use to make sale signs. I'm using mail merge to pull the product brands, sizes, sale prices, savings, etc from excel. There is a mix of font sizes and styles all throughout the file.There are four signs (records) per page. I'm using a macro to format the sale prices, because I want the "cents" portion of the price to be superscript and a smaller font size than the "dollar" portion. I'm using the macro, because the sale price is a single cell in excel and gets pulled into word with uniform formatting. The way that I got my find/replace working is to have the sale price italicized and have italics be my gating condition for the search.

The problem now is that I have a second, similar file with slightly different requirements, and I'd like to handle with this same macro. I want the find/replace to act on all italicized text. If that text is not bolded, then set the cents to a certain font size. If the text IS bolded, then the cents should be set to a different size.

At the end of the macro, I remove italics and bold from the sale price.

Here's what I have, along with some pseudocode at the branching point. This has to be a simple operation, but I can't get it to work. Can you help?



Sub Format_4up_Sale_Price()'
' Format_4up_Sale_Price Macro
'
'

Dim newsize As Integer



Selection.Find.ClearFormatting
Selection.Find.Font.Italic = True
Selection.Find.Replacement.ClearFormatting

With Selection.Find
.Text = ".[0-9][0-9]"
.Replacement.Text = ""
.Forward = True
.Wrap = wdFindContinue
.Format = True
.MatchCase = False
.MatchWholeWord = False
.MatchAllWordForms = False
.MatchSoundsLike = False
.MatchWildcards = True

End With



'====== Code in question ========

'pseudo code:
' If Selection is bold
' newsize = 90
' Else
' newsize = 82
' End If
'end pseudocode

With Selection.Find.Replacement.Font
.Size = newsize
.Superscript = True
.Subscript = False
End With

'====== End question ========



Selection.Find.Execute Replace:=wdReplaceAll
Selection.Find.ClearFormatting
Selection.Find.Font.Italic = True
Selection.Find.Replacement.ClearFormatting



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

Selection.Find.Execute Replace:=wdReplaceAll
Selection.Find.ClearFormatting
Selection.Find.Font.Italic = True
Selection.Find.Replacement.ClearFormatting
Selection.Find.Replacement.Font.Italic = False
Selection.Find.Replacement.Font.Bold = False

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

Selection.Find.Execute Replace:=wdReplaceAll

End Sub

[macropod]

It seems to me you'd do better to have the $ and ¢ data in separate cells, or use a formula field in the mailmerge main document to separate them, so that no post-processing is required. A pair of field codes to separate the $ and ¢ portions for differential formatting is:
{QUOTE{SET Val «Amount»{=INT(Val) \# "$,0." }\* Charformat{=MOD(Val,1) \# "00" \* Charformat}}
where «Amount» is whatever mergefield you're using to output the value. With this construction, you merely format each of the '=' characters with whatever attributes you want the corresponding portion of the output to have. Done. No VBA required.

Note: The field brace pairs (i.e. '{ }') for the above example are created in the document itself, via Ctrl-F9 (Cmd-F9 on a Mac); you can't simply type them or copy & paste them from this message. Nor is it practicable to add them via any of the standard Word dialogues. Likewise, you can't type or copy & paste the chevrons (i.e. '« »') - they're part of the actual mergefields, which you can insert from the mailmerge toolbar. The spaces represented in the field construction are all required.

[jason456]

Thanks, that worked for me after fixing a couple of syntax errors. Here's what I have:
{ QUOTE { SET Val { MERGEFIELD Sale_Price_ \#0.0x } }{ =INT(Val) \# # \* CharFormat } { =MOD(VAL,1) \# ".##" \* CharFormat}}

I'm still relying on a macro to remove the decimal point, though. My signs look like this: [ $399 ], and I couldn't find a number formatting parameter that could leave off the decimal without rounding to an integer. (3.99 becoming 301). Can that be done?

You solved my problem, and I thank you very much for that. I'm still interested in how this would be done in VB, though. Having this super long formula and then setting the font size for parts of it to 110 makes it impossible to efficiently work on the formula itself. Plus, if I want to tweak my font styling, I have to make four edits (four signs per page) instead of making one change to the macro.

I'll wait to mark this as solved, because I want to see if there's a solution to my original question.

Thanks,
Jason.

[macropod]

If you don't want the period, use:
{QUOTE{Set Val {MERGEFIELD Sale_Price_ \#0.00}}{=INT(Val) \# 0 \* CharFormat}{=MOD(VAL,1)*100 \# 00 \* CharFormat}}
As for:

setting the font size for parts of it to 110 makes it impossible to efficiently work on the formula itself
I did say you need only apply the formatting to the '=' signs ...

Yes, it could be done with a macro, but I can't see why anyone would bother when a final result can be obtained without one. After all, how often do you need to edit the field, vs how often would you need to both modify and run the macro?

[jason456]

Yes, you did say to apply the formatting to only the '=' signs, and that's what I did. I said that I'm dealing with font sizes in the 90 to 110 range, though. Even with the rest of the formula set to 1 point font, they're pushing my other fields out into never never land. And what is wrong with wanting to centralize my formatting into a single file rather than peppering it into multiple places among multiple files?

I would like to know how to read the properties of a selection in VBA for my own personal edification. I spent a fair amount of time trying to learn it in place, and it's going to nag at me forever if I don't figure it out. I've accepted that this thread isn't the place for me, though.

Thank you for your help. Due to your assistance, I'm better off than I was. However, there's always more that I want to know. Good day.

P.S. I'm not marking this as solved, because while you solved my problem, you didn't address my question. I don't want to mislead someone who has a similar need and stumbles on this thread using a search engine.

[macropod]

See: http://www.vbaexpress.com/forum/showthread.php?50356-Word-2013-Macro-to-Highlight-Excel-List-of-Prepositions-Only-at-End-of-Sentences&p=313135&viewfull=1#post313135 . The macro there shows how to use Find replace to locate a string and then conditionally do something with it. Do note that, as that code shows, nothing need ever be selected. Code that works with selections, as yours does, it quite inefficient by comparison.

I don't really care whether you mark the problem as solved. The Admins may take a different view. The fact is, as you admit, the answers I've given have solved the problem.