asked on 9/12/2013

Format column and fill with time

Hello,

I have a column "EA" containing delivery times, as general format, example:

1018
1001
943

I need to show these time in column "DZ" in the time format,
10:18 AM
10:01 AM
9:43 AM

I know the formula is =TIME(INT(EA2/100),MOD(EA2,100),0)

How can use vba to insert this formula in DZ column and fill down to length of EA column?

Thank You

Microsoft Excel

Last Comment

ExpressMan1

9/13/2013

ButlerTechnology

9/12/2013

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

Open in new window

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

Tom

ExpressMan1

9/12/2013

ASKER

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

ButlerTechnology

9/12/2013

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

ExpressMan1

9/12/2013

ASKER

Excel 2007. File attached
FedEx-9.xlsx

ASKER CERTIFIED SOLUTION

ButlerTechnology

9/13/2013

THIS SOLUTION IS ONLY AVAILABLE TO MEMBERS.

View this solution by signing up for a free trial.

Members can start a 7-Day free trial and enjoy unlimited access to the platform.

See Pricing Options

Start Free Trial

ExpressMan1

9/13/2013

ASKER

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

ExpressMan1

9/13/2013

ASKER

Correction, all values are showing 10:18 AM

ButlerTechnology

9/13/2013

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

ExpressMan1

9/13/2013

ASKER

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?

ExpressMan1

9/13/2013

ASKER

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