Be seen. Boost your questionâ€™s priority for more expert views and faster solutions
Dim strTime As String
Set rg = Intersect(Range("G:G"), ActiveSheet.UsedRange)
For Each c In rg.Cells
If IsNumeric(c) Then
Select Case Len(c)
Case 1
strTime = "00:0" & c
Case 2
strTime = "00:" & c
Case 3
strTime = Left(c, 1) & ":" & Right(c, 2)
Case 4
strTime = Left(c, 2) & ":" & Right(c, 2)
End Select
c = strTime
End If
Next c
Sub test()
Dim strTime As String
Set rg = Range("G5:G8")
For Each c In rg.Cells
If IsNumeric(c) Then
Select Case Len(c)
Case 1
strTime = "00:0" & c
Case 2
strTime = "00:" & c
Case 3
strTime = "0" & Left(c, 1) & ":" & Right(c, 2)
Case 4
strTime = Left(c, 2) & ":" & Right(c, 2)
End Select
c.Value = "'" & strTime
End If
Next c
End Sub
Sub test()
Dim strTime As String
Set rg = Range("G5:G8")
For Each c In rg.Cells
If IsNumeric(c.Value) And c.Value <> vbNullString Then
Select Case Len(c)
Case 1
strTime = "00:0" & c
Case 2
strTime = "00:" & c
Case 3
strTime = "0" & Left(c, 1) & ":" & Right(c, 2)
Case 4
strTime = Left(c, 2) & ":" & Right(c, 2)
End Select
c.Value = "'" & strTime
strTime = vbNullString
End If
Next c
End Sub
Private Sub test2()
Dim rg As Range
Dim c As Range
Dim timeVal As Date
Set rg = Range("G5:G8")
For Each c In rg
If c < 1000 Then
timeVal = TimeValue("0" & Left(c, 1) & ":" & Right(c, 2))
End If
If c >= 1000 Then
timeVal = TimeValue(Left(c, 2) & ":" & Right(c, 2))
End If
c = timeVal
c.NumberFormat = "hh:mm;@"
Next c
End Sub
Set rg = Range("G5:G8")
to Set rg = Range("W:W")
Sub test()
Dim strTime As String
Set rg = Intersect(Range("W:W"), ActiveSheet.UsedRange)
For Each c In rg.Cells
If IsNumeric(c.Value) And c.Value <> vbNullString Then
Select Case Len(c)
Case 1
strTime = "00:0" & c
Case 2
strTime = "00:" & c
Case 3
strTime = "0" & Left(c, 1) & ":" & Right(c, 2)
Case 4
strTime = Left(c, 2) & ":" & Right(c, 2)
End Select
c.Value = "'" & strTime
strTime = vbNullString
End If
Next c
End Sub
Sub test3()
Dim strTime As String
Set rg = Intersect(Range("W:W"), ActiveSheet.UsedRange)
rg.Offset(, 1).Formula = "=SUMPRODUCT(--TEXT(RC[-1],""00\:00""))"
rg.Offset(, 1).NumberFormat = "hh:mm;@"
rg.Offset(, 1).Value = rg.Offset(, 1).Value
rg.Formula = "=TEXT(RC[1],""HH:MM"")"
rg.Copy
rg.PasteSpecial xlPasteValues
Application.CutCopyMode = False
rg.Offset(, 1).ClearContents
End Sub
Sub test3()
Dim strTime As String
Set rg = Intersect(Range("W:W"), ActiveSheet.UsedRange)
rg.Offset(, 100).Formula = "=SUMPRODUCT(--TEXT(RC[-100],""00\:00""))"
'rg.Offset(, 1).NumberFormat = "hh:mm;@"
rg.Offset(, 100).Value = rg.Offset(, 100).Value
rg.Formula = "=TEXT(RC[100],""HH:MM"")"
rg.Copy
rg.PasteSpecial xlPasteValues
Application.CutCopyMode = False
rg.Offset(, 100).ClearContents
End Sub
c.value = format(strTime,"0:00")