Solved: Leading Zero with a twist

[blzbub]

Hi all,

I have a problem that's driving me insane, I've lost track of the number of test VBA code I've tried and copied and pasted to try and sort it.

I have data which is drawn from an AVAYA product showing timings for staff work times.

In the report the times show as HH:MM:SS as they are intended, i.e.

10:02:32 for 10 hours, 2 minutes and 32 seconds,
00:25:23 for 25 minutes and 23 seconds.

Cool.

The problem is that when the data is exported to Excel the second example above shows as :25:23 and I need the leading zero's put back in.

Ideally some code which selects a range, checks each cell to see if it starts with a number or ":" and if it starts with ":" it adds "00" to the start.

Absolutely any help or tips would be appreciated.

It's a lot to ask for a new member's first post but it might save my sanity.

Cheers (and pleased to meet you all),

[Bob Phillips]

[vba]

Public Sub ProcessData()
Dim i As Long
Dim LastRow As Long

With ActiveSheet

LastRow = .Cells(.Rows.Count, "A").End(xlUp).Row
For i = 1 To LastRow

If Not IsNumeric(.Cells(i, "A").Value) Then

.Cells(i, "A").Value = TimeValue("0" & .Cells(i, "A").Value)
End If
Next i
End With

End Sub
[/vba]

[blzbub]

You rock, absolutely perfect, respect and best wishes,

Neil.

[blzbub]

I have simplified this and drive it from a button so I can drag out a selection and activate a few times depending on spread of data.

Very pleased with this result, xld you are a star.


[VBA]Private Sub CommandButton1_Click()
For Each cell In Selection
If Not IsNumeric(cell.Value) Then
cell.Value = TimeValue("0" & cell.Value)
End If
Next cell
End Sub
[/VBA]

Thank you so much