PDA

View Full Version : Replace dots with commas



kekn
09-10-2010, 12:31 PM
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:

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

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 :wot (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 :banghead:

/kekn

Bob Phillips
09-10-2010, 01:32 PM
Hard to test as our settings are different, but try this



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

kekn
09-11-2010, 02:32 AM
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 :dunno

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
09-11-2010, 03:27 AM
Hi there,

Just another try:

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
IF (a big if) it works, I think it could be used vba/udf and single/array of cells.

Hope it works...

Mark