We help IT Professionals succeed at work.

Check VBA Macro (2)

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 1 : https://www.experts-exchange.com/questions/29166936/Check-VBA-macro-1.html

MACRO 2 : I use this one A LOT too, also "on the go" or within others macro. It's simple, whenever I got any error with numbers stored as text, I just click on the column and launch this :

Sub TextToNumbers()
    '
    ' ToNumbers Macro
    ' Converts the selected column to numbers (when numbers stored as text)
    Range(Selection, Selection.End(xlDown)).Select
    With Selection
        Selection.NumberFormat = General
        .Value = .Value
    End With
End Sub
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
"There is still no cure for the common birthday." ~John Glenn
Most Valuable Expert 2017
Distinguished Expert 2018
Commented:
It should be fine as is.
Magpie BavardeExecutive Assistant

Author

Commented:
Thank you very much Martin ! Good to know as I use it reaaaally often ^^
byundtMechanical Engineer
Most Valuable Expert 2013
Top Expert 2013
Commented:
Unless General is a public constant (or variable), the original code causes a runtime error.
Sub TextToNumbers()
    '
    ' ToNumbers Macro
    ' Converts the selected column to numbers (when numbers stored as text)
    Dim rg As Range
    Set rg = ActiveCell
    Set rg = Range(rg, rg.End(xlDown))
    rg.NumberFormat = "General"
    rg.Value = rg.Value
End Sub

Open in new window

byundtMechanical Engineer
Most Valuable Expert 2013
Top Expert 2013
Commented:
In the suggestions I have made to the four macros, I consistently replaced Selection with ActiveCell. This works when there are no blank cells within your range. If there are one or more blank cells in the original selection, you will want to replace my ActiveCell with Selection.

So the preceding suggestion would become:
Sub TextToNumbers()
    '
    ' ToNumbers Macro
    ' Converts the selected column to numbers (when numbers stored as text)
    Dim rg As Range
    Set rg = Selection
    Set rg = Range(rg, rg.End(xlDown))
    rg.NumberFormat = "General"
    rg.Value = rg.Value
End Sub

Open in new window