VBA Replace not working for numbers over 1.0

Question

I'm trying to make a macro that takes the selected cells, replace the "." with ",", then changes it to double, divides it by 2 and rounds it off.

Examples of cells:

0.910 1.000

For Each Cell In Selection
    Cell.Value = Replace(Cell, ".", ",")
    Cell = CDbl(Cell.Value)
    Cell = Cell / 2
    Cell = Round(Cell, 4)
Next Cell

I'm expecting to get:

0.4550 0.5000

The problem is that if I have a number greater than 1.0 it just removes the "." and doesn't replace it with anything and since the cells have 3 decimals, I suddenly get 1000 instead of 1.

So I get:

0.4550 500

Any suggestions of what goes wrong?

Why are you replacing the "." with a ","? You are getting what you ask for; replacing "," with ".", and then doubling the value, will give you 1000. for any number that is grater than 1.0 You can get what you want by just reformatting the cells to show only 1 decimal place. — GMalc, Dec 07 '17 at 16:33
Or just use Round function. See [Link](https://stackoverflow.com/questions/16017501/how-to-round-up-with-excel-vba-round) — GMalc, Dec 07 '17 at 16:44
@SJR - Because the computer thinks 1.000 = 1000 instead of 1. — Makknozz, Dec 08 '17 at 10:24
@GMalc59 - This might be because I'm Swedish and we are using "," as a decimal mark. For numbers less than 1, my code works. The "." has to be replaced with a "," somehow, although my solution doesn't seem to work for numbers greater than 1. If I'm reformatting to only one decimal then I won't get the right result for numbers less than 1, right? — Makknozz, Dec 08 '17 at 10:24
@GMalc59 - The problem is still that my computer doesn't understand that the "." is a decimal marker and not a "thousand marker". — Makknozz, Dec 08 '17 at 10:24
@Makknozz There appears to be a `Application.DecimalSeparator` and `Application.UseSystemSeparators` setting. Could you change these? Only problem is it appears this is application-wide. Link: https://msdn.microsoft.com/en-us/vba/excel-vba/articles/application-decimalseparator-property-excel — Brownish Monster, Dec 08 '17 at 10:37

Xabier · Answer 1 · 2017-12-08T10:57:50.137

0

How about:

Sub foo2()
Dim cell As Range
Dim value As Double
    For Each cell In Selection
        cell = Application.RoundUp(cell, 1)
        cell = CStr(cell.value)
        cell.NumberFormat = "@"
        If cell.value <> "1" Then
            cell.value = cell.value & "00"
        Else
            cell.value = cell.value & ",000"
        End If
        cell.value = Replace(cell.value, ".", ",")
    Next cell
End Sub

edited Dec 08 '17 at 10:57

answered Dec 07 '17 at 17:36

Xabier

7,587
1
8
20

The problem is that my computer doesn't understand that the "." is a decimal marker and not a "thousand marker". Reformatting doesn't seem to solve this. – Makknozz Dec 08 '17 at 10:25

score 0 · Answer 2 · answered Dec 07 '17 at 19:54

0

A little simpler; credited to @njimack

For Each cell In Selection
    If IsNumeric(cell) Then cell.Formula = Round(cell.Value, 1)
Next cell

answered Dec 07 '17 at 19:54

GMalc

2,608
1
9
16

Limon · Answer 3 · 2022-08-10T13:49:35.343

The subject is a little bit old, but i was working on the problem and i got something working. I'm working on a macro that get data from .csv and i manage with this :

    Cells.Select
    Selection.Replace What:=".", Replacement:=".", LookAt:=xlPart, _
    SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
    ReplaceFormat:=False

Excel will understand that the number in the cell are number, and because my default setting are with "," and not "." my final data was with "," at the right place.

Limon

VBA Replace not working for numbers over 1.0

3 Answers3