c.value = format(strTime,"0:00")
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
Title | # Comments | Views | Activity |
---|---|---|---|
Excel - Scroll Speed | 3 | 26 | |
Excel Autofill Dropdown List with Combobox : How to make use of Tab and Enter key to input a value. | 5 | 20 | |
Convert .PDF | 6 | 42 | |
In Excel 2007, how do I insert a blank space between two dynamic values in a cell? | 3 | 10 |
Join the community of 500,000 technology professionals and ask your questions.
Connect with top rated Experts
20 Experts available now in Live!