Replace function on selected range

[GerrardSVK]

Hello all.

please can anyone help me with replace function?
I have just simple range selected with numbers but I need to change dot into comma and degree mark with nothing See picture bellow.
Under I wrote my code but do not work properly somwhere its ok somewhere not.

 

[6StringJazzer]

Is your input data numeric or text?

 

[kvsrinivasamurthy]

Book1
	F	G	H	I
1
2		23.465	23,465
3		3.78	3,78
4		0.7689	0,7689
5		3455.89	3455,89
6		0.00563	0,00563
7		0.0402	0,0402
8
Sheet3

Cell Formulas
Range	Formula
H2:H7	H2	=SUBSTITUTE(G2,".",",")

In H2 copied down

=SUBSTITUTE(G2,".",",")

 

[GerrardSVK]

Is your input data numeric or text?

they are generated in CSV that I lately use for input into another exel so I dont know how they are taken where I can find it? Unexpected is that formula that I used works just particulary so on some of the same format data it works and on some of them not works.

 

[GerrardSVK]

Book1
	F	G	H	I
1
2		23.465	23,465
3		3.78	3,78
4		0.7689	0,7689
5		3455.89	3455,89
6		0.00563	0,00563
7		0.0402	0,0402
8
Sheet3

Cell Formulas
Range	Formula
H2:H7	H2	=SUBSTITUTE(G2,".",",")

In H2 copied down

=SUBSTITUTE(G2,".",",")

I need to do it to whole selection cause there is a lot of operations done and selection changes by a time so I need some VBA code that will work for my selection.

 

[kvsrinivasamurthy]

Try this code. Works on active sheet.
A free cell (Fcel) is used for calculation purpose.

Sub ChangeToComma()
Dim R As Range, Cel As Range, Fcel As Range, K As Double
On Error GoTo Line1
Set Fcel = Range(InputBox("Enter a free cell address for calculation :" & Chr(10) & Chr(10) & "EG: AC10", "FREE CELL ENTRY"))
If Fcel.Cells.Count > 1 Then Exit Sub
Set R = Range(InputBox("Enter the range for change :" & Chr(10) & Chr(10) & "EG: C5:F10", "RANGE ENTRY"))
For Each Cel In R
Fcel.Formula = "=substitute(" & Cel.Address & ",""."","","")"
Cel = Fcel
Next Cel
Fcel = ""

Line1:
End Sub

 

[kvsrinivasamurthy]

Making code slightly faster.

Sub ChangeToComma()
Dim R As Range, Cel As Range, Fcel As Range, K As Double
Application.ScreenUpdating = False
On Error GoTo Line1
Set Fcel = Range(InputBox("Enter a free cell address for calculation :" & Chr(10) & Chr(10) & "EG: AC10", "FREE CELL ENTRY"))
If Fcel.Cells.Count > 1 Then Goto Line1
Set R = Range(InputBox("Enter the range for change :" & Chr(10) & Chr(10) & "EG: C5:F10", "RANGE ENTRY"))
For Each Cel In R
Fcel.Formula = "=substitute(" & Cel.Address & ",""."","","")"
Cel = Fcel
Next Cel
Fcel = ""
Line1:
Application.ScreenUpdating = True
End Sub

 

[kvsrinivasamurthy]

Try this code. This code does not use any helper cell.

Sub ChangeToComma()
Dim M, R As Range, Cel As Range
Application.ScreenUpdating = False
On Error GoTo Line1
Set R = Range(InputBox("Enter the range for change :" & Chr(10) & Chr(10) & "EG: C5:F10", "RANGE ENTRY"))
For Each Cel In R
M = Split(Cel.Value, ".")
If UBound(M) = 0 Then
Cel = M(0)
Else
Cel = M(0) & "," & M(1)
End If
Next Cel
Fcel = ""
Line1:
Application.ScreenUpdating = True
End Sub

 

[GerrardSVK]

Making code slightly faster.

Sub ChangeToComma()
Dim R As Range, Cel As Range, Fcel As Range, K As Double
Application.ScreenUpdating = False
On Error GoTo Line1
Set Fcel = Range(InputBox("Enter a free cell address for calculation :" & Chr(10) & Chr(10) & "EG: AC10", "FREE CELL ENTRY"))
If Fcel.Cells.Count > 1 Then Goto Line1
Set R = Range(InputBox("Enter the range for change :" & Chr(10) & Chr(10) & "EG: C5:F10", "RANGE ENTRY"))
For Each Cel In R
Fcel.Formula = "=substitute(" & Cel.Address & ",""."","","")"
Cel = Fcel
[QUOTE="kvsrinivasamurthy, post: 6253174, member: 262811"]
Try this code. This code does not use any helper cell.
[CODE=vba]
Sub ChangeToComma()
Dim M, R As Range, Cel As Range
Application.ScreenUpdating = False
On Error GoTo Line1
Set R = Range(InputBox("Enter the range for change :" & Chr(10) & Chr(10) & "EG: C5:F10", "RANGE ENTRY"))
For Each Cel In R
M = Split(Cel.Value, ".")
If UBound(M) = 0 Then
Cel = M(0)
Else
Cel = M(0) & "," & M(1)
End If
Next Cel
Fcel = ""
Line1:
Application.ScreenUpdating = True
End Sub

I try it but still result is bad it seems that exel takes number that are 0.123 as numbers with decimal point and numbers like 23.456 takes like numbers where decimal points is thousand divider and I dont know why **** it. :D

[/QUOTE]
Hello it seems to complicated I found that problem is that data I have are not considered same so for example number 23.464 is taken as integer because after calling my simple replace funkcion it is taken as 23 464 otherwise number started with 0.somthing is taken probably as double so after conversion it is right format

 

[kvsrinivasamurthy]

Try

Sub ChangeToComma()
Dim M, R As Range, Cel As Range
Application.ScreenUpdating = False
On Error GoTo Line1
Set R = Range(InputBox("Enter the range for change :" & Chr(10) & Chr(10) & "EG: C5:F10", "RANGE ENTRY"))
R.NumberFormat = "@"
For Each Cel In R
M = Split(Cel.Value, ".")
If UBound(M) = 0 Then
Cel = M(0)
Else
Cel = M(0) & "," & M(1)
End If
Next Cel
Fcel = ""
Line1:
Application.ScreenUpdating = True
End Sub