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

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:

Please Login or Register  to view this content.

Here is the subroutine in its entirety:

Please Login or Register  to view this content.

Any suggestions or help is greatly appreciated.

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)?