Format with numbers problem!

Handril

New Member
Joined
Oct 14, 2017
Messages
2
Hello, guys!

I'm trying to covert a column full of numbers like 1234,56 or 987.654 to this format 1234.56, max 2 decimals and "." instead of ",". But when I try to find the "," the method completely ignores it...

I tried to change the format from General to Numbers, Text etc., but no success

The code is not very good yet, first I'm trying to solve this problem. My code look likes this:

(Thanks in advance)

For Each cell In campoCol.Rows

c = cell.Value
d = InStr(1, c, ".", vbTextCompare) ' FIND DECIMAL POINT
If d > 0 And Len(c) > (d + 2) Then ' MORE THAN 2 DECIMAL NUMBERS
MsgBox ("2 decimals only")
End If

Set valorVirgula = Selection.Find(What:=",", SearchOrder:=xlByRows)

If Not valorVirgula Is Nothing Then
Do
valorVirgula.Replace What:=",", Replacement:=".", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=True, SearchFormat:=False, _
ReplaceFormat:=False
Set valorVirgula = Selection.FindNext(valorVirgula)
Loop While Not valorVirgula Is Nothing
End If
 

Excel Facts

Links? Where??
If Excel says you have links but you can't find them, go to Formulas, Name Manager. Look for old links to dead workbooks & delete.

mole999

Moderator
Joined
Oct 23, 2004
Messages
10,524
Office Version
  1. 2019
  2. 2016
  3. 2013
Platform
  1. Windows
, and . are influenced by system / language settings
 

mole999

Moderator
Joined
Oct 23, 2004
Messages
10,524
Office Version
  1. 2019
  2. 2016
  3. 2013
Platform
  1. Windows
in the excel options window you can set defaults, or use the system settings. It depends on what you need to do. For me I want UK regional settings and then stick with those. If you change what is the default for something you like the look of, then its unlikely to work as you want it to.
 

Forum statistics

Threads
1,136,635
Messages
5,676,902
Members
419,657
Latest member
ExcelAl1

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top