Converting Currency Format in Word

[Goblin82]

Hi, I'm trying to make a macro to change a currency format from english to french. For example $1.00 to 1,00$ but I need to do this for any number. I figured I would just do a search and replace in three steps but the problem is I can't have to change something in the text that looks like 1.0 and change that period to a comma. Also I would like to have this macro only process on the selected text, the problem I have with my code below is that the macro will run on the selected text for the first search and replace but then do the rest of the document for the second and third step. I was wondering if you guys had any ideas on how to accomplish this in an easier way or even just solve my problem on the selected text portion, here is my code so far:


Selection.Find.ClearFormatting
Selection.Find.Replacement.ClearFormatting
With Selection.Find
.Text = "$([0-9])"
.Replacement.Text = "\1"
.Forward = True
.Wrap = wdFindStop
.Format = False
.MatchCase = False
.MatchWholeWord = False
.MatchWildcards = True
.MatchSoundsLike = False
.MatchAllWordForms = False
End With
Selection.Find.Execute Replace:=wdReplaceAll

With Selection.Find
.Text = "([0-9]),([0-9])"
.Replacement.Text = "\1^s\2"
.Forward = True
.Wrap = wdFindStop
.Format = False
.MatchCase = False
.MatchWholeWord = False
.MatchWildcards = True
.MatchSoundsLike = False
.MatchAllWordForms = False
End With
Selection.Find.Execute Replace:=wdReplaceAll

With Selection.Find
.Text = "([0-9]).([0-9])([0-9])"
.Replacement.Text = "\1,\2\3^s$"
.Forward = True
.Wrap = wdFindStop
.Format = False
.MatchCase = False
.MatchWholeWord = False
.MatchWildcards = True
.MatchSoundsLike = False
.MatchAllWordForms = False
End With
Selection.Find.Execute Replace:=wdReplaceAll

[Tinbendr]

Using range...
Dim Rng As Range
Set Rng = ActiveDocument.Range

With Rng.Find
.ClearFormatting
.Replacement.ClearFormatting
.Text = "$([0-9])"
.Replacement.Text = "\1"
.Forward = True
.Wrap = wdFindStop
.Format = False
.MatchCase = False
.MatchWholeWord = False
.MatchWildcards = True
.MatchSoundsLike = False
.MatchAllWordForms = False
.Execute Replace:=wdReplaceAll
End With

With Rng.Find
.Text = "([0-9]),([0-9])"
.Replacement.Text = "\1^s\2"
.Forward = True
.Wrap = wdFindStop
.Format = False
.MatchCase = False
.MatchWholeWord = False
.MatchWildcards = True
.MatchSoundsLike = False
.MatchAllWordForms = False
.Execute Replace:=wdReplaceAll
End With

With Rng.Find
.Text = "([0-9]).([0-9])([0-9])"
.Replacement.Text = "\1,\2\3^s$"
.Forward = True
.Wrap = wdFindStop
.Format = False
.MatchCase = False
.MatchWholeWord = False
.MatchWildcards = True
.MatchSoundsLike = False
.MatchAllWordForms = False
.Execute Replace:=wdReplaceAll
End With

[Goblin82]

doesn't seem to be working, going through step by step, I'm highlighting one portion of text but the macro ends up selecting the whole text and running the macro througout the whole document anyway.

[Tinbendr]

but the problem is I can't have to change something in the text that looks like 1.0 and change that period to a comma.That's right. I forgot about this.

You can't use Selection because when the code gets to Execute, the selection is collapse after the replacement.

Maybe, put a msgbox as ask whether to proceed or not, but it'll have to be rewritten to take out the wdreplaceall, because it will, well, Replace All occurrences.

Back to the drawing board.

[Tinbendr]

Ok, found this message (http://www.vbaexpress.com/forum/showthread.php?p=230003) by Greg Maxey. Since it finds the whole number format, I just modified it.

I tried it with a 'Version 1.0' in the text and it didn't pick it up. You might be able to remove the msgbox and run it unimpeded.

David

Sub ChangeFormat()
'Modified by David Sisson 11/27/10
'http://www.vbaexpress.com/forum/showthread.php?t=35160
'A quick macro scratch pad created by Greg Maxey
'http://www.vbaexpress.com/forum/showthread.php?p=230003
Dim oRng As Word.Range
Dim Ans$ 'as string
Set oRng = ActiveDocument.Range
With oRng.Find
.Text = "$[0-9.,]{1,}" 'Find the currency pattern
.MatchWildcards = True
While .Execute
With oRng.Duplicate.Find
oRng.Select
Ans$ = MsgBox(oRng & vbCr & vbCr & "Change this one?", vbYesNo, "Change Format")
If Ans$ = 6 Then
oRng = Replace(oRng, ",", " ")
oRng = Replace(oRng, ".", ",")
oRng = Right(oRng, Len(oRng) - 1) & "$"
oRng.Collapse wdCollapseEnd
End If
End With
Wend
End With
End Sub

[Goblin82]

that's awesome! it works quite well. I'll probably use it as is as I have been unable to only run this on a selection made by the user. I've been trying to save the selection and then run the code while within that selection, I don't have enough experience with VBA to accomplish this but I'll continue tinkering with the code and see if I can figure it out. Thanks for all your help! :D

[Tinbendr]

been trying to save the selection and then run the code while within that selectionI didn't think of this before, but you can store the start and end points of the selection. Maybe that is what you're looking for.

Dim SelStart as Long
Dim SelEnd as Long

SelStart = Selection.Start
SelEnd = Selection.End
'First replace section here
Selection.Start = SelStart
Selection.End = SelEnd
'next part

[fumei]

One way to prevent further processing beyond the selected text, is to test against the Selection.End.

If you set the range object to be the whole document:

Set oRng = ActiveDocument.Range

then it will process the whole document, unless otherwise told not to.

Also note Greg's use of Duplicate.

[Goblin82]

I'm not really following the last few examples. Even if I set oRng to something else it processes the document in full either way, even if I remove Duplicate it still runs through the whole document. I've also tried fiddling a bit with Selection.Start and Selection.End but the problem I'm having is it seems to replace the whole selected text and not just the currency formats within that selection.

The other test I've tried was to set a range from the selection instead of the whole document but once the replacement starts the selection dissapears and the macro replaces every instance in the document.

[Tinbendr]

This works with the example in the doc. Select one amount; it only changes that selection. Select two amounts; it only changes that selection.