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
ExpressMan1Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

ButlerTechnologyCommented:
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
0
ExpressMan1Author Commented:
After I run this the range of cells in "DZ" column are all showing just "10"
0
ButlerTechnologyCommented:
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
0
Angular Fundamentals

Learn the fundamentals of Angular 2, a JavaScript framework for developing dynamic single page applications.

ExpressMan1Author Commented:
Excel 2007.  File attached
FedEx-9.xlsx
0
ButlerTechnologyCommented:
Change the column format to General and try again.  The custom format that is set on the column is the root of the issue for formatting.  Resetting to General and running the code with adjust the column format.

Tom
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
ExpressMan1Author Commented:
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
0
ExpressMan1Author Commented:
Correction, all values are showing 10:18 AM
0
ButlerTechnologyCommented:
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
0
ExpressMan1Author Commented:
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?
0
ExpressMan1Author Commented:
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
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.