PDA

View Full Version : [SOLVED] VBA for Excel: automated correction of wrong decimal separator



mdaneri
09-26-2017, 11:56 PM
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

macropod
09-27-2017, 12:31 AM
Cross-posted at: https://www.excelforum.com/excel-programming-vba-macros/1202465-vba-for-excel-automated-correction-of-wrong-decimal-separator.html
Please read VBA Express' policy on Cross-Posting in item 3 of the rules: http://www.vbaexpress.com/forum/faq.php?faq=new_faq_item#faq_new_faq_item3

mdaneri
09-27-2017, 12:39 AM
TY Paul. Read the thread. Won't happen again.
Marco

mdmackillop
09-27-2017, 03:27 AM
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

mdaneri
09-27-2017, 04:14 AM
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

mdaneri
09-27-2017, 02:14 PM
:bow::bow::bow:
Thanks a lot mdmackillop,
as you probably already know, it worked perfectly!

Marco

[This thread can be closed]

Paul_Hossler
09-27-2017, 04:47 PM
: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

Paul_Hossler
09-27-2017, 07:12 PM
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

20496
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

mdaneri
09-28-2017, 02:20 AM
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