Replace $123,234,345 with $ xxx,xxx,xxx

[nehahazra]

Please help me with a VB macro which will help me replacing the pricing details. That is i want if there is a price mentioned $123,234,45 then it can be replaced by $ xxx,xxx,xx I have a word doc of 100 odd pages with various pricing details mentioned i have to cleansed the pricing details with "X" and not loose the format.

[gmaxey]

Try:

Sub ScratchMacro()
'A quick macro scratch pad created by Greg Maxey
Dim oRng As Word.Range
Set oRng = ActiveDocument.Range
With oRng.Find
.Text = "$[0-9.,]{1,}" 'Find the currency pattern
.MatchWildcards = True
While .Execute
With oRng.Duplicate.Find
.Text = "[0-9]" 'find the numbers within the currency pattern
.Replacement.Text = "x"
.MatchWildcards = True
.Execute Replace:=wdReplaceAll
oRng.Collapse wdCollapseEnd
End With
Wend
End With
End Sub

[Paul_Hossler]

Can you explain the purpose of the .Duplicate?


With oRng.Duplicate.Find


Don't think I've ever seen that

Paul

[fumei]

Duplicate is most often used to preserve formatting.

[gmaxey]

Paul,

I wish I could give a concise technical explaination but since I have no formal education or training in programming I can't:(

Take a look at what happens with the code if you don't use Rng.Duplicate.Find in the second find operation. If you have numbers like:

I want to buy 4 books for $9.99 and 3 shirts for $34.99 and 100 widgets for $3.99 then "all" the numbers are changed to "x"

VBA does strange things with the range object during find operations. It collapses it to the "found" text then exands it again to the beginning of the found text to the .wrap point. That is why if you are processing found ranges one at a time (i.e. in While .Execute loop) and not changing the found text you have to use orng.Collapse wdCollapseEnd after each iteration.

Using oRng.Duplicate locks the found range on the original found text so the next .find is only looking at "$9.99" then "$34.99" then "$3.99".

I wish I could offer a better explaination.

While I don't know how it works really, I do know that often times when something isn't working like I think it shouls with oRng, I will use oRng.Duplicate and "Eureka!!" it works.






Can you explain the purpose of the .Duplicate?


With oRng.Duplicate.Find


Don't think I've ever seen that

Paul

[gmaxey]

Gerry,

Being better than I, I was hoping that you would do better than that :devil2:


Duplicate is most often used to preserve formatting.