Converting Unformatted string into Time Value (VBA; error on one line of code)

[aloha31]

I'm attempting to convert a column of data (exported from a database so its unformatted) into a time format recognizable by Excel.
i.e. I want to change "804a" to "8:04 AM" or "645p" to "6:45 PM"

I'm receiving a run-time error "Application-defined or object-defined error" on this line:

cell.Formula = "=Time(Left(" & cell.Value & ", 1), Left(Right(" & cell.Value & ", 3), 2), 0)"

Here is the subroutine in its entirety:

Sub colon()

Dim lastcolumn As Integer
Dim lastrow As Integer
Dim cell As Range




'Find last column of data
With ActiveWorkbook.ActiveSheet.Range("A1")
    lastcolumn = Cells(2, Columns.Count).End(xlToLeft).Column
    lastrow = Cells(Rows.Count, lastcolumn).End(xlUp).Row

    Range(.Offset(1, lastcolumn - 2), .Offset(lastrow, lastcolumn - 2)).Name = "combined"
    
End With


'Convert simple time into formatted time
For Each cell In Range("combined")

    If Len(cell) = 4 Then

        cell.Formula = "=Time(Left(" & cell.Value & ", 1), Left(Right(" & cell.Value & ", 3), 2), 0)"
    End If
Next
           
    
End Sub

Any suggestions or help is greatly appreciated.

[Leith Ross]

Hello aloha31,

Welcome to the Forum!

Sub colon()

Dim lastcolumn As Integer
Dim lastrow As Integer
Dim cell As Range
Dim n As Long


'Find last column of data
With ActiveWorkbook.ActiveSheet.Range("A1")
    lastcolumn = Cells(2, Columns.Count).End(xlToLeft).Column
    lastrow = Cells(Rows.Count, lastcolumn).End(xlUp).Row

    Range(.Offset(1, lastcolumn - 2), .Offset(lastrow, lastcolumn - 2)).Name = "combined"
    
End With


'Convert simple time into formatted time
For Each cell In Range("combined")

    n = Len(cell)
    If n = 4 Or n = 5 Then
        cell = Left(cell, n - 3) & ":" & Right(cell, 3)
        cell.NumberFormat = "h:mm AM/PM"
    End If
Next
            
End Sub

[aloha31]

Thanks so much! Works great. One additional question: Is there a way to undo the effects of a macro once you run it (the undo button becomes grayed out after running the subroutine)?

[Leith Ross]

Hello alhoa31,

With a VBA macro, there is no Undo feature.