CODEWITHSUNDEEP
vbaexcel
CeFu
CeFu

Reputation: 149

Excel VBA Converting seconds to HH:MM:SS

Trying to convert specific data in a specific column through the whole worksheet.

This is my current code, it does not error out, however it doesn't display the first result correctly (Shows 00:00:00), and only stops at the first record.

Sub CleanEntry()
    Dim i As Integer
    Dim Seconds As Integer
    Dim j As Long
Dim c As Long
j = 2

For i = Sheet1.UsedRange.Rows.Count To 1 Step -1
c = Range("B" & j).Value
c = c / 86400
Range("B" & j).Value = Format(c, "hh:mm:ss")
j = j + 1
Next
End Sub

Upvotes: 0

Views: 4004

Answers (1)

Scott Craner
Scott Craner

Reputation: 152660

Though I do not understand why you are using two counters, with one going backwards and the other forward, this can be done with one line:

Sub CleanEntry()
    Dim i As Long
    Dim j As Long

j = 2

For i = Sheet1.UsedRange.Rows.Count To 1 Step -1
    Range("B" & j).Value = Format(TimeSerial(0, 0, Range("B" & j).Value), "hh:mm:ss")
    j = j + 1
Next
End Sub

Upvotes: 5

Related Questions