Check VBA Macro (3)

Magpie Bavarde
Magpie Bavarde used Ask the Experts™
on
Hello the experts,

I'm not good with VBA but I use it a lot, I usually record some stuff then ask my friend  google about what I can't get recorded. I know all my macros are probably very weak but as long as I'm the only one at risk it's not an issue. But this time some colleagues are interested and I don't want to "sell" them a rotten file. So... Would you please have a look on my macros and let me know if you see something that should be improved so I can share my file with good chances it will work for other people ? Your corrections are obviously welcome, but also your advices to help me understand why I should do this and not do that...

Thank you very much for your help, Kind regards,

Mélanie


MACRO 3 : This one is used only on my expanses files, to get the numbers on the desired format (Accounting, 2 decimals). There's a lot of "try & fail" behind what I suppose should be very simple and easy. Note that I'm french and for whatever insane reason we use a comma instead of a dot before the decimals. So I start by eliminating the damn commas and replace them by dots, then I apply the desired formatting. I get a feeling this macro is very wrong. But so far it does the job for me...

Sub ToNumbers()
    Range(Selection, Selection.End(xlDown)).Select
    With Selection
        Selection.Replace What:=",", Replacement:=".", LookAt:=xlPart, _
                          SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
                          ReplaceFormat:=False
    End With
    Range(Selection, Selection.End(xlDown)).Select
    With Selection
        Selection.NumberFormat = "_(* #,##0.00_);_(* (#,##0.00);_(* ""-""??_);_(@_)"
    End With
End Sub

Open in new window

Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Martin Liss"Life would be infinitely happier if we could only be born at the age of eighty and gradually approach eighteen." - Mark Twain
Most Valuable Expert 2017
Distinguished Expert 2018

Commented:
I wrapped your macro in CODE tags to make it easier to talk about. IMO you should always do that. Anyhow, what happens if you delete lines 3 to 7?

BTW what Regional setting do you use?
Magpie BavardeExecutive Assistant

Author

Commented:
Thank you very much Martin ! I'm editing my threads to do that everywhere.

Im on US regional setting on a very stubborn machine that WANTS TO BE FRENCH NO MATTER WHAT.

If I delete lines 3 to 7 it just doesnt work... but the only important part here is to replace the commas by dots

I'm pretty confident it wouldnt change much if I just went with

Sub ToNumbers()
    Range(Selection, Selection.End(xlDown)).Select
    With Selection
        Selection.Replace What:=",", Replacement:="."
    End With
    Range(Selection, Selection.End(xlDown)).Select
    With Selection
        Selection.NumberFormat = "_(* #,##0.00_);_(* (#,##0.00);_(* ""-""??_);_(@_)"
    End With
End Sub

Open in new window

Martin Liss"Life would be infinitely happier if we could only be born at the age of eighty and gradually approach eighteen." - Mark Twain
Most Valuable Expert 2017
Distinguished Expert 2018

Commented:
Can you show me a picture of a cell that has been subject to the macro in your original question?
Mechanical Engineer
Most Valuable Expert 2013
Top Expert 2013
Commented:
I removed the Selection from the macro. It converts text that looks like 123456,32 into a number like 123,456.32
Sub ToNumbers()
    Dim rg As Range
    Set rg = ActiveCell
    Set rg = Range(rg, rg.End(xlDown))
    rg.Replace What:=",", Replacement:=".", LookAt:=xlPart, _
                          SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, ReplaceFormat:=False
    rg.NumberFormat = "_(* #,##0.00_);_(* (#,##0.00);_(* ""-""??_);_(@_)"
End Sub

Open in new window

byundtMechanical Engineer
Most Valuable Expert 2013
Top Expert 2013
Commented:
If the selected range of cells contains blanks, using ActiveCell as shown above would not give the desired results. In such a case, use Selection.
Sub ToNumbers()
    Dim rg As Range
    Set rg = Selection
    Set rg = Range(rg, rg.End(xlDown))
    rg.Replace What:=",", Replacement:=".", LookAt:=xlPart, _
                          SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, ReplaceFormat:=False
    rg.NumberFormat = "_(* #,##0.00_);_(* (#,##0.00);_(* ""-""??_);_(@_)"
End Sub

Open in new window

Magpie BavardeExecutive Assistant

Author

Commented:
Thank you Byundt !

Martin, here's what a typical use of those macros looks like for me :

... aaaand I dont find any way to attach my screenshot.

Damn.

Will try to edit the first post
byundtMechanical Engineer
Most Valuable Expert 2013
Top Expert 2013
Commented:
if you save your screenshot as a .png (best choice) or .jpg file, you may attach it to a Comment or Question box just like you attach an Excel file. When you do so, make sure you complete all the steps, as the file won't post if you omit even something as inconsequential as not providing a brief description in the field next to the uploaded file.
Martin Liss"Life would be infinitely happier if we could only be born at the age of eighty and gradually approach eighteen." - Mark Twain
Most Valuable Expert 2017
Distinguished Expert 2018
Commented:
as the file won't post if you omit even something as inconsequential as not providing a brief description
That used to be the case but it was changed a while back and it's no longer required.
Martin Liss"Life would be infinitely happier if we could only be born at the age of eighty and gradually approach eighteen." - Mark Twain
Most Valuable Expert 2017
Distinguished Expert 2018

Commented:
I’m glad I was able to help.

If you expand the “Full Biography" section of my profile you’ll find links to some articles I’ve written that may interest you.

Marty - Microsoft MVP 2009 to 2017
              Experts Exchange Most Valuable Expert (MVE) 2015, 2017
              Experts Exchange Top Expert Visual Basic Classic 2012 to 2018
              Experts Exchange Top Expert VBA 2018
              Experts Exchange Distinguished Expert in Excel 2018

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial