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

Last used cell?
Press Ctrl+End to move to what Excel thinks is the last used cell.
, and . are influenced by system / language settings
 
Upvote 0
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.
 
Upvote 0

Forum statistics

Threads
1,213,497
Messages
6,113,998
Members
448,539
Latest member
alex78

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
Back
Top