Change of decimal separator in VBA doesn't produce a number

[J Laroche]

My international preferences are set for a comma as the decimal separator.
However imported data often contains a period as decimal separator, and I
wrote a macro to make the conversion. Unfortunately, contrary to what
happens when the conversion is made by hand with Edit/Replace, cell data
remains as text after the macro has executed.

The macro does:
With ActiveSheet
.Range("E2:M367").Replace What:=".", Replacement:=",", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False

When I do that operation manually (in fact, when I record the macro), a cell
containing -12.80 becomes -12,8. But via macro the cell becomes -12,80,
remains left-aligned and doesn't respond to any number formatting, even by
hand. The only way to make it behave like a number is by double-clicking on
the cell and just hitting Enter. Needless to say this is impractical with
the number of cells I'd need to convert.

I found out that a Paste Special with a multiplication by 1 could convert
the texts to numerical values. So I added:

.Range("C2").Copy ' value 1
.Range("E2:M367").PasteSpecial Paste:=xlAll, Operation:=xlMultiply, _
SkipBlanks:=False, Transpose:=False

Again, this works perfectly by hand, but has no effect via macro.

Finally, I had to resort to:
For Each c In .Range("E2:M367").Cells
If IsNumeric(c.Value) Then c.Value = c.Value + 0
Next

I works, but it's much longer.

Does anybody have a better method?


JL
Mac OS X 10.3.9, Office v.X 10.1.6

 

[JE McGimpsey]

Finally, I had to resort to:
For Each c In .Range("E2:M367").Cells
If IsNumeric(c.Value) Then c.Value = c.Value + 0
Next

I works, but it's much longer.

Does anybody have a better method?

I wasn't able to reproduce that unless the cells were formatted as Text.
So this might work:

With ActiveSheet
With .Range("E2:M367")
.NumberFormat = "General"
.Replace What:=",", Replacement:=".", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False
End With
End With


Or, perhaps:

With ActiveSheet
On Error Resume Next
With .Range("E2:M367").SpecialCells( _
xlCellTypeConstants, xlTextValues)
.NumberFormat = "General"
.Replace What:=",", Replacement:=".", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False
.Value = .Value
End With
On Error GoTo 0
End With

 

[Bernard Rey]

These won't work (well, they don't here, French Excel/French Mac OS). That's
a rather "classical" situation: Excel VBA is not localized when the
worksheet is. Replacing the period with a comma can't force the string to
number, as VBA will consider a number with comma as text. Well I don't know
if this makes much sense, but I've experienced this many times. So you just
have to treat each cell separately at one point or another.

Another way to do it could be:

Dim c As Range
Application.ScreenUpdating = False
With ActiveSheet
For Each c In .Range("E2:M367").SpecialCells( _
xlCellTypeConstants, xlTextValues)
c.Replace What:=".", Replacement:=",", LookAt:=xlPart
c.Value = c.Value + 0
Next c
End With

 

[Davide Cantoni Dichgans]

I have a problem with the decimal separator...

My international preferences are set for a comma as the decimal separator.

.... and that's just the point. Where on earth, in which menu do I find the
"International Prefs"?
I can't get my German version of Xcel to understand American-born datasets,
that is datasets with a period as a decimal separator

Tks

Davide

MacOS X 10.3.4
Office v.X SR 1

 

[JE McGimpsey]

My international preferences are set for a comma as the decimal separator.

... and that's just the point. Where on earth, in which menu do I find the
"International Prefs"?
I can't get my German version of Xcel to understand American-born datasets,
that is datasets with a period as a decimal separator[/QUOTE]

It's in your system preferences. In OS 10.3.x or 10.4.x, you can access
System Preferences from the Apple menu. The International preferences
pane is in the Personal group at the top of the System Preferences
dialog.