Format column and fill with time

The follow code should work -- I am assuming that the first row has headings.

Public Sub TimeStuff()
Dim EACC As Integer
EACC = 0
Range("EA1").Activate
Do
  EACC = EACC + 1
  ActiveCell.Offset(1, 0).Activate
Loop Until ActiveCell.Value = ""

Range("DZ2").FormulaR1C1 = "=TIME(INT(RC[1]/100),MOD(RC[1],100),0)"
Range("DZ2").AutoFill Destination:=Range("DZ2:DZ" & EACC)
Range("DZ2").Activate

End Sub

Select allOpen in new window

There is probably a more elegant way to determine number of rows.

Tom

After I run this the range of cells in "DZ" column are all showing just "10"

Very weird -- I just tried it on my system with success.  What version of Excel are you using?  What is the format for those target cells.  They start off as general on mine and are switched to custom h:mm AM/PM  Any change that you could post the spreadsheet?  Just having the DZ and EA columns would work for testing.  I find that when I work with made up data that everything works until I get the real data.

Tom

Excel 2007.  File attached
FedEx-9.xlsx

That did seem to help but now all the results in column DZ show 10:18 which is the value of EA2 (1018),

But not taking the adjacent time values in column EA,

1018
1001
943

etc

Correction, all values are showing 10:18 AM

It sounds like it is doing a copy instead of an auto fill of it is using absolute reference.

What is the formula in cell DZ3 after you run the macro?

Tom

Thanks very much for your help.

I just saved the sheet and the values updated correctly. All the formulas were showing correctly in the DZ cells but only updated when saved. I imagine there is an simple line of code to save the sheet?

Formula in DZ3 was correct    =TIME(INT(EA3/100),MOD(EA3,100),0)

I added this line to the end of your code and it updated correctly,

Columns ("DZ") .Formula = Columns ("DZ") .Formula