Replace dots with commas

[kekn]

Hi

I'm new to VBA and new to these forums. I registered here in hope of some help with my (at the moment) very simple code.

So, I have Excel 2010 (think the problem applies for 2007 as well, though). The thing is, Excel uses commas instead of dots... at least here in Denmark. And I have a LOT of data, going like this:

583.74
582.7
582.81
581
574.04
587.73
573.01
...

So I made a macro that could do the replacing for me... simple enough. Here is the code on a small area:

[VBA]Sub Makro1()
Range("A1:A6").Select
Selection.Replace What:=".", Replacement:=",", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
End Sub[/VBA]

It makes the replacement alright, but apparently still recognize it as text. Selecting a numbers format doesn't help, but pressing F2 and enter solves it (also, there's an error showing up next to the cells saying that the number is formatted as text... pressing "convert to number" solves it too)

Here's a simple made sheet that shows the process... try running the macro and behold:

EDIT: Ok, I can't post links yet... gotta play by the rules, right?

Help a newbie do this small thing

/kekn

[Bob Phillips]

Hard to test as our settings are different, but try this

[vba]

Sub Makro1()
With Range("A1:A7")
.Replace What:=".", Replacement:=",", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
.Value = .Value
End With
End Sub
[/vba]

[kekn]

That didn't help, but I think solved it now. Apparently the problem occurs because of language issues... or that's my guess. For example:

Cells(5, 5) = "7,5"
Cells(5, 6) = "7.5"

The first number will be returned as text and the second will actually be returned as "7,5", yes, replaced automatically with a comma!!! ... and in a numbers format. So however useless my code now seems... it works

Sub Makro1()
Range("A1:A6").Select
Selection.Replace What:=".", Replacement:=".", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
End Sub

Thanks for helping out

[GTO]

Hi there,

Just another try:

[VBA]Option Explicit

Sub exa()
With Range("A1:A7")
.NumberFormat = "0.00"
.Value = SwapDecSep(Range("A1:A7"))
End With
End Sub

Function SwapDecSep(ByRef CellRange As Range) As Variant
Static REX As Object '<--- RegExp
Dim CellStrings As Variant
Dim x As Long
Dim y As Long

CellStrings = CellRange.Value

Set REX = CreateObject("VBScript.RegExp")
With REX
.Global = False
.Pattern = ","
If IsArray(CellStrings) Then
For x = 1 To UBound(CellStrings)
For y = 1 To UBound(CellStrings, 2)
CellStrings(x, y) = CDbl(.Replace(CellStrings(x, y), "."))
Next
Next
Else
CellStrings = CDbl(.Replace(CellStrings, "."))
End If
SwapDecSep = CellStrings
End With
End Function[/vba]
IF (a big if) it works, I think it could be used vba/udf and single/array of cells.

Hope it works...

Mark