[computerman29642]

I am trying to figure out the best way to copy a value from one cell, and paste it into another cell as a time value.

The cell to be copied can have values such as:

1152
930
450
0823

What I want to do is place a colon between the first two numbers and the last two numbers. I have code that does this but it fails when there is not a leading zero.

How would I do this be determining if there is a leading zero or not?

[computerman29642]

Here is the solution I have come up with...

If Len(wsTest.Range("L6")) = 4 Then
                wsTest1.Cells(x, "E") = Left(wsTest.Range("L6"), 2) _
                & ":" & Right(wsTest.Range("L6"), 2)
            Else
                wsTest1.Cells(x, "E") = Left(wsTest.Range("L6"), 1) _
                & ":" & Right(wsTest.Range("L6"), 2)
            End If

[Zack Barresse]

You could do it with one line. I added the test for a date so you wouldn't duplicate the action, but just do it based on the length from the right minus 2. There is no check for overall length or if it is a number, or even if the cell has a value, so lots of errors to think about...

    If IsDate(wsTest.Range("L6").Value) = False Then
        wsTest.Range("L6").Value = Left(wsTest.Range("L6").Value, Len(wsTest.Range("L6").Value) - 2) & ":" & Right(wsTest.Range("L6").Value, 2)
    End If

Edit: Here is a simpler looking code by assigning the value to a variable (looks easier to understand a little shortened up)...

Sub testAddColon()

    Dim wsTest As Worksheet, iColon As Long, vVal As Variant
    
    Set wsTest = ActiveSheet
    vVal = wsTest.Range("L6").Value
    
    If IsDate(vVal) = False Then
        vVal = Left(vVal, Len(vVal) - 2) & ":" & Right(vVal, 2)
    End If

End Sub

HTH

[computerman29642]

Thanks Zack.