HH:MM code
Posted by May on July 12, 2000 8:28 AM
How can you change this code to look into say column C and change all numbers in Column D to the HH:MM format i.e. inserting the :
This code only does Column A
Cheers
May
Dim Time As String
Dim x As Integer
Application.ScreenUpdating = False
x = 0
Do
x = x + 1
Time = Cells(x, 1).Value
If Len(Time) > 3 Then
Cells(x, 2).Value = Left(Time, 2) & ":" & Right(Time, 2)
ElseIf Len(Time) = 3 Then
With Cells(x, 2)
.Value = "0" & Left(Time, 1) & ":" & Right(Time, 2)
.NumberFormat = "hh:mm"
End With
End If
Loop While Cells(x + 1, 1).Value <> ""
Application.ScreenUpdating = True
Posted by May on July 13, 0100 1:56 AM
Error
I'm sorry.
I don't know why but it seems to be looking for something in Column A (I accidently left the same data there and I thought it did Column C but didn't).
I've copied the code but as I said it's looking for something in Col A
Any thoughts?
May
Posted by Ivan Moala on July 13, 0100 2:23 AM
Re: Error
May
Change line that reads;
Loop While Cells(x + 1, 1).Value <> "" TO
Loop While Cells(x + 1, 3).Value <> ""
Ivan
Posted by Ryan on July 13, 0100 5:26 AM
Thanks Ivan
Skipped right over that last one. SOrry about that.
Posted by Ivan Moala on July 13, 0100 6:16 AM
Re: Thanks Ivan
Ryan
Thats easy to do....I do it a lot :-) especially
if you answer a lot of Q as you do.
Ivan
Posted by Ryan on July 12, 0100 8:36 AM
May,
This will do column C into D. Whereever it says Cells(x,1), the "1" is designating what column to look in or put a value into. So 3 = C and 4 = D, you can change this however you need it. Let me know if this is what you want!
Ryan
Dim Time As String
Dim x As Integer
Application.ScreenUpdating = False
x = 0
Do
x = x + 1
Time = Cells(x, 3).Value
If Len(Time) > 3 Then
Cells(x, 4).Value = Left(Time, 2) & ":" & Right(Time, 2)
ElseIf Len(Time) = 3 Then
With Cells(x, 4)
.Value = "0" & Left(Time, 1) & ":" & Right(Time, 2)
.NumberFormat = "hh:mm"
End With
End If
Loop While Cells(x + 1, 1).Value <> ""
Application.ScreenUpdating = True
Posted by May on July 12, 0100 9:00 AM
Thank you for your speedy response.
May.
