I have a solution now. I would like to know if anyone has
suggestions for improvement. Specifically, is it possible
to end up with the following result "in-place" without
first creating a temporary column to do the work, then
deleting it?
Here is my VBA. Note that Col. E contains the date. Col. F
contains either hh:mm:ss or a 5-or-6-digit "fake" replica of
time that my below formula converts to hh:mm:ss. (See a recent
thread in microsoft.public.excel.misc for where that came from.)
My replacement Col. F will combine the two values in one and format
it as hh:mm:ss. (I will later use this column to sort the sheet
chronologically.)
I am making a temporary Col. G to do the calculation and then
paste it back over the current Col. F.
Note, also, that "LastRow" is a function not shown here. I
got it from Ron de Bruin's sheet-merge pages.
'==================
Private Sub TimeFix()
Dim myRgF, myRgG As Range
Const myFormula = "=INDIRECT(""E""&ROW())+IF(INDIRECT(""F""&ROW())>1,TEXT(INDIRECT(""F""&ROW()),""0\:00\:00"")+0,INDIRECT(""F""&ROW()))"
With Application
.ScreenUpdating = False
.EnableEvents = False
End With
Columns("G:G").Insert Shift:=xlToRight
Set myRgF = Range("F3", Cells(LastRow(ActiveSheet), "F"))
Set myRgG = myRgF.Offset(0, 1)
myRgG.Formula = myFormula
myRgG.Copy
myRgF.Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Application.CutCopyMode = False
Selection.NumberFormat = "h:mm:ss;@"
Columns("G:G").Select
Selection.Delete Shift:=xlToLeft
With Application
.ScreenUpdating = True
.EnableEvents = True
End With
End Sub
'==================
