How to replace Comma by Point in VBA

Asked

Viewed 2,132 times

0

How do I replace a comma with a dot using VBA?

The following macro does not work:

Range("F2").Select
Range(Selection, Selection.End(xlDown)).Select
Selection.Replace What:=",", Replacement:=".", LookAt:=xlPart, _
    SearchOrder:=xlByColumns, MatchCase:=False, 
    SearchFormat:=False, _
    ReplaceFormat:=False

inserir a descrição da imagem aqui

vba excel-vba

  • You can format the entire column in Excel as number with decimal divisor "." , manually in Excel and without VBA. But if you want to use VBA see the function Format

2 answers

0

something like this ?

Sub teste()
 Dim valor_range As Range
    
     For Each valor_range In ActiveSheet.Range("A1:Z500")
        valor_range.Replace What:=",", Replacement:=".", MatchCase:=True
     Next
     
End Sub

In the desired range search for "," and replace with "."

0

Your problem is quite simple: you are mixing garlic with oranges. You are looking for a string (",") inside a cell that contains a non-string value.

I would bet high that the prices in column F are formatted as number. Result: Excel searches the string ",", finds none to replace and returns no error or makes any changes to the spreadsheet.

For your code to work without any changes, just turn the values into strings (open the cell and insert a single quote ' at the beginning, before the value; does not need space. That is: instead of 2,39, place '2,39, and so on. Voilà!

That is, the macro works; just doesn’t do what you want.

If you just want to change the format so that the decimals are marked by a dot and the thousands' squares are marked by a comma, you have to change the way the decimals are marked, not the values contained.

I can only imagine two reasons to do this: an international presentation or using a google sheet.

If you need to paste in a Google spreadsheet, I think this conversion is already done automatically. For an international presentation, just search how to change decimal and thousands markup in Excel -- but it is recommended to tinker with it VERY carefully, because you can generate compatibility issues on other computers, and both you and other users (if the computer is used by other people) may end up creating confusion in values reaching the order of thousands of reais!

Browser other questions tagged vba excel-vba

You are not signed in. Login or sign up in order to post.