EXCEL FORMULA: VBA: to change a format

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

Select allOpen in new window

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?

PERFECT!!  What a clean script too!!  Awesome..  Thanks David!

glad I can help.