Link to home
Create AccountLog in
Avatar of Maliki Hassani
Maliki HassaniFlag for United States of America

asked on

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
Avatar of david251
david251

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

Avatar of Maliki Hassani

ASKER

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.
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
So what if I wanted it to look through a range, sheet 1 at Range("E4:M73")?
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.
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
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..
Perhaps like an if then statement will do it, not sure?
ASKER CERTIFIED SOLUTION
Avatar of david251
david251

Link to home
membership
Create a free account to see this answer
Signing up is free and takes 30 seconds. No credit card required.
See answer
PERFECT!!  What a clean script too!!  Awesome..  Thanks David!
glad I can help.