EXCEL FORMULA: VBA: to change a format

Experts,

Can someone help me out with how to write a VBA code to change the format of numbers like this....          :16:50 to .1650

It has to be done in a VBA code though.

I have attached a spreadsheet for example CHANGE-NUMBER.xlsm
Maliki HassaniAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

david251Commented:
Try the code below.  Let me know if you need any other help.
Function reFormatNumber(strTemp As String) As Double
'usage: =reFormatNumber(A2)
'Note: no error chicking is being performed

'replace the : with a blank and add the decimal portion
    reFormatNumber = CDbl("0." & Replace(strTemp, ":", ""))

End Function

Open in new window

Maliki HassaniAuthor Commented:
David:

How can I run this function?  Can you attach the file I uploaded with the code in it so I can see how to run it?  The code will be added a macro that I running, at the end of the macro.
david251Commented:
in cell c2 type this:
=reFormatNumber(A2)


If you are using it in your macro you can try something like this

worksheets("Sheet1").Range("C2") = reFormatNumber(worksheets("Sheet1").Range("A2"))
CHANGE-NUMBER.xlsm
Your Guide to Achieving IT Business Success

The IT Service Excellence Tool Kit has best practices to keep your clients happy and business booming. Inside, you’ll find everything you need to increase client satisfaction and retention, become more competitive, and increase your overall success.

Maliki HassaniAuthor Commented:
So what if I wanted it to look through a range, sheet 1 at Range("E4:M73")?
Maliki HassaniAuthor Commented:
David:  See the sheet that I am using will have regual r numbers like 146, .24,123,  So it will need to be able to look at a range and find the cells that start with : and chnage them like you have done.
david251Commented:
you need to create a loop in your macro.  Are all of you values in the range that Format (:00:00)?  

If yes, than you can use a loop like this (this assumes you data is in sheet2):



Function reFormatNumber(strTemp As String) As Double
'usage: =reFormatNumber(A2)
'Note: no error chicking is being performed

'replace the : with a blank and add the decimal portion
    reFormatNumber = CDbl("0." & Replace(strTemp, ":", ""))

End Function


Sub reformatLoop()
    Dim c As Range
    For Each c In Worksheets("Sheet2").Range("E4:M73").Cells
        c.Value = reFormatNumber(c.Value)
        c.NumberFormat = "0.0000"
    Next c

End Sub
Maliki HassaniAuthor Commented:
David:  My previous reply stated that they are different formats, which makes it harder.   That is why I am thinking it needs to look for the first character to be : and then it will do the reformatting..
Maliki HassaniAuthor Commented:
Perhaps like an if then statement will do it, not sure?
david251Commented:
Try this instead:
Function reFormatNumber(strTemp As String) As Double
'usage: =reFormatNumber(A2)
'Note: no error chicking is being performed

'replace the : with a blank and add the decimal portion
    reFormatNumber = CDbl("0." & Replace(strTemp, ":", ""))
End Function


Sub reformatLoop()
    Dim c As Range
    For Each c In Worksheets("Sheet2").Range("E4:M73").Cells
        If Mid(c.Value, 1, 1) = ":" _
            And IsNumeric(Mid(c.Value, 2, 2)) _
            And Mid(c.Value, 4, 1) = ":" _
            And IsNumeric(Mid(c.Value, 5, 2)) _
            And Len(c.Value) = 6 Then
                c.Value = reFormatNumber(c.Value)
                c.NumberFormat = "0.0000"
        End If
    Next c

End Sub

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Maliki HassaniAuthor Commented:
PERFECT!!  What a clean script too!!  Awesome..  Thanks David!
david251Commented:
glad I can help.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Visual Basic Classic

From novice to tech pro — start learning today.