Not wdReplaceAll but what?

[Opinicus]

I've written the following macro to convert numbers in
Turkish style to English style:

<quote>
Sub TRENumber()
'
' TRENumber Macro
'
Selection.Find.ClearFormatting
Selection.Find.Replacement.ClearFormatting
With Selection.Find
.Text = ","
.Replacement.Text = "|"
.Forward = True
.Wrap = wdFindContinue
.Format = False
.MatchCase = False
.MatchWholeWord = False
.MatchWildcards = False
.MatchSoundsLike = False
.MatchAllWordForms = False
End With
Selection.Find.Execute Replace:=wdReplaceAll
With Selection.Find
.Text = "."
.Replacement.Text = ","
.Forward = True
.Wrap = wdFindContinue
.Format = False
.MatchCase = False
.MatchWholeWord = False
.MatchWildcards = False
.MatchSoundsLike = False
.MatchAllWordForms = False
End With
Selection.Find.Execute Replace:=wdReplaceAll
With Selection.Find
.Text = "|"
.Replacement.Text = ","
.Forward = True
.Wrap = wdFindContinue
.Format = False
.MatchCase = False
.MatchWholeWord = False
.MatchWildcards = False
.MatchSoundsLike = False
.MatchAllWordForms = False
End With
Selection.Find.Execute Replace:=wdReplaceAll
End Sub
</quote>

It's pretty straightforward. First it converts all "," to
"|"; then all "." to ","; then all "|" to ".".

A number like 1.234.567,89 (Turkish style) end up as
"1,234,567.89" (English style).

The trouble is that Replace:=wdReplaceAll is a global
command so *all* the commas and periods in the document get
exchanged for one another. This is not good. I only want the
macro to work on the text that I've highlighted.

How do I do this?

TIA.

 

[Greg Maxey]

Opinicus,

How about:
Sub ChangeFormatOfSelectedNumber()

If Selection.Type = wdSelectionIP Then
MsgBox "Select text before running this macro.", , "Nothing Selected"
End
End If
'Strip separators
With Selection.Find
.ClearFormatting
.Replacement.ClearFormatting
.Wrap = wdFindStop
.MatchWildcards = True
.Text = "[!0-9]"
.Replacement.Text = ""
.Execute Replace:=wdReplaceAll
End With
'Format
With Selection
.Text = Format(.Text, "#,###0.00")
If Left(.Text, 1) = "-" Then
.Text = "(" & Replace(.Text, "-", "") & ") "
End If
End With

End Sub

 

[Helmut Weber]

Hi Opinicus,

highlighted or selected, as Greg assumed?

Greetings from Bavaria, Germany

Helmut Weber, MVP
"red.sys" & chr(64) & "t-online.de"
Word XP, Win 98
http://word.mvps.org/

 

[Opinicus]

highlighted or selected

Highlighted ("defined" with control-right arrow, for
example).

 

[Helmut Weber]

If you mean shift ! right arrow,
the Greg's method (untested) would be the right way,
I guess.

Greetings from Bavaria, Germany

Helmut Weber, MVP
"red.sys" & chr(64) & "t-online.de"
Word XP, Win 98
http://word.mvps.org/

 

[Opinicus]

How about:
Sub ChangeFormatOfSelectedNumber()

This is good Greg but it always adds ".00" even if there is
no ",00" in the original. The desired conversion is like
this:


1.234.567 >> 1,234,567
1.234.567,89 >> 1,234,567.89

Can that be done?

 

[Greg Maxey]

Bob,

Yes. This might be a bit crude, but for single selection it appears to do
the desired deed.

Option Explicit
Sub ChangeFormatOfSelectedNumber()
Dim pDecimal As Boolean
Dim myRange As Range
pDecimal = False
If Selection.Type = wdSelectionIP Then
MsgBox "Select text before running this macro.", , "Nothing Selected"
End
End If
Set myRange = Selection.Range
If InStrRev(Selection.Text, ",") > 0 Then
pDecimal = True
Else: pDecimal = False
End If
'Strip separators
With Selection.Find
.ClearFormatting
.Replacement.ClearFormatting
.Wrap = wdFindStop
.MatchWildcards = True
.Text = "[!0-9]"
.Replacement.Text = ""
.Execute Replace:=wdReplaceAll
End With
'Format
MsgBox myRange
If pDecimal Then
With myRange
.Text = Format(.Text, "#,###0.00")
If Left(.Text, 1) = "-" Then
.Text = "(" & Replace(.Text, "-", "") & ") "
End If
End With
Else
With Selection
.Text = Format(.Text, "#,###0")
If Left(.Text, 1) = "-" Then
.Text = "(" & Replace(.Text, "-", "") & ") "
End If
End With
End If

End Sub

 

[Greg Maxey]

Bob,

Here is another method that seems works pretty well:

Sub ChangeFormatOfSelectedNumber()
Dim myRange As Range
If Selection.Type = wdSelectionIP Then
MsgBox "Select text before running this macro.", , "Nothing Selected"
End
End If
Set myRange = Selection.Range
myRange = Replace(myRange, ".", "+")
myRange = Replace(myRange, ",", "*")
myRange = Replace(myRange, "*", ".")
myRange = Replace(myRange, "+", ",")
With myRange
If Left(.Text, 1) = "-" Then
.Text = "(" & Replace(.Text, "-", "") & ") "
End If
End With
End Sub

 

[Opinicus]

Greg

Here is another method that seems works pretty well:

Sub ChangeFormatOfSelectedNumber()
Dim myRange As Range
If Selection.Type = wdSelectionIP Then
MsgBox "Select text before running this macro.", ,
"Nothing Selected"
End
End If
Set myRange = Selection.Range
myRange = Replace(myRange, ".", "+")
myRange = Replace(myRange, ",", "*")
myRange = Replace(myRange, "*", ".")
myRange = Replace(myRange, "+", ",")
With myRange
If Left(.Text, 1) = "-" Then
.Text = "(" & Replace(.Text, "-", "") & ") "
End If
End With
End Sub

Excellent! It even toggles the "." and "," back and forth.

This seems to be the heart of it:

Set myRange = Selection.Range
myRange = Replace(myRange, ".", "+")

This effectively replaces wdReplaceAll, am I right?

Is there a book or a CD or something where I can learn more
about VBA? (Preferably with exercises etc.) I was pretty
good at Pascal and Quickbasic but there's so much about VBA
that I don't even begin to understand.

Thanks again.

 

[Greg Maxey]

Bob,

I am a budding beginner with VBA. I have never read a book on VBA. So I
have no "Try my product" line to offer. I learned what I know by reading
and these newsgroups, finding something I want to try to solve, pull hair,
ask a lot of questions, and sometimes find a solution. You will find F1 a
great resource. Highlight Replace in the code and press F1. It will open a
help screen explaining it.

This seems to be the heart of it:

Set myRange = Selection.Range
myRange = Replace(myRange, ".", "+")

Now that you know about Replace. What I did was first change the "." in the
selection to an arbitrary chacter "+" unlikely to be found in a numberical
string, then did the same with the ",". Next just changed what was a "." to
a "," and what was a "," to a "."

This effectively replaces wdReplaceAll, am I right?

Here is where I speculate. In this case yes it effectively replace
wdReplaceAll. However (and I didn't test), since Replace( ) is performing
a process on a string, I think the limit is 255 characters.

A classical VBA expert may be along to educate us both