VBA for Excel: automated correction of wrong decimal separator

Hello all,
1st, let me thank you for the great job you are doing with this forum: I use it a lot to grab examples as I am quite newbie in VBA programming.


I'd have a request for a sample code to be implemented in a wider spreadsheet.


I copy and paste a set of data from a webpage to an Excel file.


The data imported as numbers have some decimal separator which from time to time is not the same of the system where I import the data.


I would like to create a macro which:


1 - Detects the decimal separator (comma or full stop) actually used by Excel: the system must work in different versions which can have different separators


2 - Replaces the commas in the whole spreadsheet with the correct decimal separator



3 - Replaces the full stops in the whole spreadsheet with the correct decimal separator



(I don't mind if the action is redundant or not efficient, as I do not operate on a too wide set of data)


4 - Formats all the columns from D to G, from the second row to the end of the spreadsheet, as numbers


So that I can later on process the data as numbers.


I can easily do it manually, but just because I know which is the right decimal separator for my Excel; I need this operation to be automated in order to work on an Excel file of any other person.


Thank you all again,



Marco

Cross-posted at: https://www.excelforum.com/excel-pro...separator.html
Please read VBA Express' policy on Cross-Posting in item 3 of the rules: http://www.vbaexpress.com/forum/faq...._new_faq_item3

TY Paul. Read the thread. Won't happen again.
Marco

Code:

---

Sub Test()    Dim r, x
    Application.ScreenUpdating = False
    x = Application.DecimalSeparator
    With ActiveSheet
        Set r = Intersect(.Range("D:G"), .UsedRange)
        With r
            .Replace What:=",", Replacement:=x, LookAt:=xlPart
            .Replace What:=".", Replacement:=x, LookAt:=xlPart
            numF = "0" & x & "00;-0" & x & "00;0" & x & "00"
            .Columns("B:D").NumberFormat = numF
            For Each cel In r.Offset(1).Columns(1).Cells
                If InStr(1, cel, "M") Then
                    cel.Replace What:="M", Replacement:="", LookAt:=xlPart
                    cel.Value = cel.Value * 10 ^ 6
                    cel.NumberFormat = "0" & x & "00,,""M"""
              End If
                If InStr(1, cel, "B") Then
                    cel.Replace What:="B", Replacement:="", LookAt:=xlPart
                    cel.Value = cel.Value * 10 ^ 9
                    cel.NumberFormat = "0" & x & "00,,,""B"""
                End If
            Next
        End With
    End With
    Application.ScreenUpdating = True
End Sub

---

TY a lot, mdmackillop, brilliant coding; I would have never guessed in short time.
I will test it this night and let you know.
Kind regards,
Marco

:bow::bow::bow:
Thanks a lot mdmackillop,
as you probably already know, it worked perfectly!

Marco

[This thread can be closed]

Quote:

---

Originally Posted by mdaneri

:bow::bow::bow:

[This thread can be closed]

---

Above your first post there's [Thread Tools]. One option you can choose is to mark this one solved

But I see that someone (or you) already did that

Are you sure that the different Regional Settings for the PC's won't do what you want?

Simple example - I have my PC set to US Region and entered data.

Just changing the region to France, Excel very nicely reformatted the way the data was displayed

Attachment 20496
Attachment 20497

So maybe import the data with the PC set to the region that generated the data, and then change to your local region

Just a thought

Hello Paul,

thanks for you idea; I had not realised such condition could happen.
Analysing your picture, it is actually quite improbable as:

- data will always come in the same form, i. e. with the same format and number of decimal digits before copy and paste to Excel
- data will be processes only once, after having been copied and pasted in Excel

Therefore it is difficult to think about changing regional settings while processing. In any case, I imagine that most of the times users manage to do unexpected things, so I will inform them in advance.

If necessary I will - probably with the help of the community - try to trap this error.

Thanks again,

Kind regards,

Marco