Excel VBA Course
Excel VBA Course - From Beginner to Expert

200+ Video Lessons
50+ Hours of Video
200+ Excel Guides

Become a master of VBA and Macros in Excel and learn how to automate all of your tasks in Excel with this online course. (No VBA experience required.)

View Course

Add a leading zero to all cells that contain a colon

0

I have a spreadsheet where I export time data from another program. The new cells are automatically formated by excel into the h:mm format.  After the data is pasted, I have a formula set up in another cell to convert it to seconds. 

The problem I am encountering is that if a value is under 1:00 excel will convert the cell to the general format, so my conversion formula will display a #VALUE error.  I can maunually had a 0 before the colon and excel will convert it to seconds without any issues. 

Is there any way to automatically add a leading zero to cells that contain a colon? 

excelformulas-functionsdate-timeformatting
MS Office Version: 2016
posted by: Harrison (rep: 2) on Fri Apr 14, 2017 at 4:54 pm
Answer
Discuss

Answers

0
Selected Answer

You might set the number format property after pasting the data.

With ActiveSheet.Columns("B")

    .Cells.Numberformat = "HH:mm:ss"

End With

I'm surprised this doesn't work for you. However, the following is an adaption of your code which you say does work to allow you to specify the columns you want reformatted. Just modify the split string "Clm" to your needs. 

Sub LeadingZero()
    ' LeadingZero Macro
    
    Dim rngData As Range
    Dim C As Long
    Dim Clms() As String
    Dim i As Integer
    
    Clms = Split("D,F,H,J,L", ",")
    For i = 0 To UBound(Clms)
        C = Columns(Clms(i)).Column
        Set rngData = Range(Cells(3, C), Cells(Rows.Count, C).End(xlUp))
        Debug.Print rngData.Address
        With rngData
            .Value = Evaluate("IF(ROW(" & .Address & "),IF(ISNUMBER(" & .Address & ")," _
                               & .Address & ",0+""00""&" & .Address & "))")
        End With
    Next i
End Sub
posted by: Variatus (rep: 4889) on Fri Apr 14, 2017 at 11:17 pm
Discuss

Discussion

I tried changing just the format without adding a zero before it, but excel still wouldn't convert it to seconds.  I found this code online, which works, but I don't know how to set it up for multiple columns.  I was able to make it work by adding a helper column, but I have some other spreadsheets with the same issue, and a helper column wouldn't work on those sheet.  Do you know how I would change the code to only change colums D, F, H, J, and L?  

Sub LeadingZero() ' ' LeadingZero Macro ' Dim rngData As Range Set rngData = Range(Cells(3, "A"), Cells(Rows.Count, "A").End(xlUp)) With rngData     .Value = Evaluate("IF(ROW(" & .Address & "),IF(ISNUMBER(" & .Address & ")," & .Address & ",0+""00""&" & .Address & "))") End With End Sub
 
Harrison (rep: 2) Apr 17, '17 at 3:03 pm
That works perfectly, thanks for the help!
Harrison (rep: 2) Apr 18, '17 at 1:15 pm
Add to Discussion


Answer the Question

You must create an account to use the forum. Create an Account or Login