Writing to a cell in a loop

[danny1572]

I'm trying to (over)write to a cell as I iterate through a loop but keep getting an error. Any ideas? I want to write the variable g to cells(i,1) and its a string... the function is to amend erroeous video time codes that hae gone over 24hr clock, eg 25:00:00:00 is 01:00:00:00 (hh:min:sec:frames)

Function timechanger()
Dim a, i As Integer
a = Application.WorksheetFunction.CountA(Range("A:A")) - 1
Dim x, y As Variant
For i = 4 To (a + 3)
x = Cells(i, 1).Text
Dim b, e, f, g As String
Dim c, d As Integer
b = Left(x, 2)
c = Int(b)
d = (c Mod 24)
e = Right(x, 9)
f = Application.WorksheetFunction.Text(d, "00")
g = f & e
Sheet2.Range(Cells(i, 1)).Text = g
Next i
End Function

[SamT]

Try this

Sub TimeChanger()
Dim i as Long
Dim LR As Long
Dim X As String

With Sheets("Sheet2")
   LR = .Cells(Rows.Count, "A").End(xlUp).Row
    For i = 4 To LR 
        X = .Cells(i, "A").Value2 
        If CInt(Left(X, 2)) > 24 Then _
       .Cells(i, "A")) = CStr(CInt(Left(X, 2)) Mod 24) & Mid(X, 3)
    Next
End If
End Sub

[danny1572]

Try this

Sub TimeChanger()
Dim i as Long
Dim LR As Long
Dim X As String

With Sheets("Sheet2")
   LR = .Cells(Rows.Count, "A").End(xlUp).Row
    For i = 4 To LR 
        X = .Cells(i, "A").Value2 
        If CInt(Left(X, 2)) > 24 Then _
       .Cells(i, "A")) = CStr(CInt(Left(X, 2)) Mod 24) & Mid(X, 3)
    Next
End If
End Sub

[danny1572]

Thanks for your response - I've attached my amended code but still have one last problem...

The times are actually in the format hh:mm:ss.frames (note the fullstop before frames not a colon), so back to the original problem of restoring a 24hr time eg 25:00:00:00 is 01:00:00:00. However, this subroutine is returning mm:ss.frames. It doesn't do it for values that don't meet the If condition.

Any ideas?

Thanks in advance...

Sub TimeChanger()
'Amend times to 24hr clock
    Dim i As Long
    Dim LR As Long
    Dim X As Variant
     
    With Sheets("Sheet1")
        LR = .Cells(Rows.Count, "B").End(xlUp).Row
        
        For i = 4 To LR
            X = .Cells(i, "B").Value2
            If CInt(Left(X, 2)) > 23 Then
                .Cells(i, "B") = "0" & CInt(Left(X, 2)) Mod 24 & Right(X, 9)
            End If
        Next i
        
        
    End With
    
End Sub

[mdmackillop]

I think you're miscounting your text length, 13, not 9. I've also changed the leading 0 to a formatting solution.

.Cells(i, "B") = Format(CInt(Left(X, 2)) Mod 24, "00") & Right(X, 13)

[danny1572]

Thanks for the reply. I don't think I am mis-counting though. The string is hh:mm:ss.frame eg 25:00:12.13 whch is 11 characters. As I'm using the modulo function to turn 25 into 01 the Right function is sticking the 9 characters from the right. The format thing you've done is really useful for the leading zero I needed.

If you run a test it is still "cropping" my result to 00:12.1 and thinks its a date. Why is excel doing this whe the cell is formatted to be general - do I need to define it as a string somehow??

Any advice would be great...

D

[mdmackillop]

:mm:ss.frames = 13 characters

[danny1572]

Apologies, by "frames" I mean two numbers, frames per second is a broadcasting term. so what i mean is hh:mm:ss.ff

[mdmackillop]

If you use "." to separate the frames, your ff is seen as decimals of a second. Using the colon or other separator will keep the cell as text, or you could format the column as text.

[danny1572]

If you use "." to separate the frames, your ff is seen as decimals of a second. Using the colon or other separator will keep the cell as text, or you could format the column as text.

The timecodes are sent in this format. What I'm confused about is why excel is happy when they are in the Spreadsheet before the subroutine is run but not afterwards. The If condition which applies the modulo and then adds the original data eg :mm:SS.ff to it for some reason makes it go haywire.

Why is this? Also, how would I format the column as text to eradicate the problem?

[mdmackillop]

Columns("B:B").NumberFormat = "@"