Receiving wrong value

Question

At the begin of my macro, I call this macro that changes all commas to dots.

Sub commaToDot()

 ActiveWorkbook.Sheets("Oficial").Activate

 Columns("B:B").Select
     Selection.Replace What:=",", Replacement:=".", LookAt:=xlPart, _
     SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
         ReplaceFormat:=False

End Sub

It works, as I can see in my data. It replaces all commas to dots from the column B.

Later in my code, I copy some of the values (which had COMMAS and now have DOTS) to other cells, but the values appear with COMMAS.

Why? I am thinking there is some memory misbehavior, but I have no idea how to work around it. Any suggestions?

Answer 1

The problem probably resides in the fact that you have different formats for your columns. Make sure your columns are properly formatted.

To force the contents of a column to be a number rather than text, try the following (in case changing column formatting doesn't have the desired effect right away) :

lastRow = Cells(rows.count, 1).end(xlup).row
For x = 1 to lastRow
    cells(x,1) = cells(x,1) + 0  'or you could do cells(x,1) = cells(x,1) / 1
Next

This will loop through all the cells of column 1 and force these to be a number.

Answer 2

My guess this is an International formatting problem. For example, if your Windows Regional Settings are Portuguese, or some other language that is using the comma as a decimal, then your problem likely relates to your VBA routine doing something to cause the destination cell to interpret the "dotted number" as a number and not as a text string.

To force Excel to interpret the "pasted" value as a string, probably the simplest method would be to precede the value to be copied with a single quote mark. This will show up in the formula bar, but not in the cell or any printouts. You could also pre-format the cell as text, but that may not be as desireable.