VBA Macro - Autofill (split a value based on hours)

**macenmin** · 04-25-2013, 02:24 PM

Hi,

I have a big problem to write a macro for the following:

What I have is 4 columns in worksheet 1:

A1: Contains a name
B1: Contains fromDate and time (formatted yyyy-mm-dd hh:mm)
C1: Contains toDate and time (formatted yyyy-mm-dd hh:mm)
D1: Contains a value

Now, the columns can have many rows with different names, dates and values - and what I want to do is to write a macro that does the following in worksheet 2:

Column A.
Put the name in column A on each row as many times as there are hours between B1 and C1 in worksheet 1.

Column B.
Autofill date and time based on B1 and C1 in worksheet 1.

Example:

2013-01-01 00:00
2013-01-01 01:00
2013-01-01 02:00
2013-01-01 03:00
2013-01-01 04:00
....
and so on down to the toDate and last hour for that row in worksheet 1.

Column C.
Autofill the divided value in D1 in worksheet 1. The value should be divided into the total amount of hours given in B1 and C1.

So if the date and time was between 2013-01-01 00:00 to 2013-01-01 04:00 and the total value was 400 in worksheet 1, the macro should put 100 in C1, C2, C3 and C4 in worksheet 2. The A column should have the name in A1, A2, A3 and A4 and the time and date should be presented as I show right over here.

I attach a sample file for how the workbook can look like. WS1 is the input sheet and WS2 is how the result should be shown when the macro is been record and started.

I hope somebody can help me with this. I would appreciate it very very much, and a big thank you in advance!! :-)

Br,
Macenmin

**mrice** · 04-25-2013, 03:02 PM

Try this on your example sheet.

Please Login or Register  to view this content.

**mehmetcik** · 04-25-2013, 05:32 PM

My Solution takes a different tack to Martins and is faster.

**macenmin** · 04-26-2013, 03:38 AM

Hi,

Thank you so much mehmetcik and mrice!

Mrice result is just what I wanted but Mehmetcik's method is much faster and i would appreciate it very much if Mehmetcik or somebody else can have another look at the macro again.

Mehmetcik :-) ->

When I run the macro, the volume goes from hour 00:00 to hour 00:00 for the same date, for eksample 2013-01-01 00:00 to 2013-01-01 (25 hr). How can I get the result to show the first value for a day as for example 2013-01-01 00:00 and the last value that day at 2013-01-01 23:00 and then the first value on the next day will go into 2013-01-02 00:00 and so on? Please see Mrice's macro/result. Its that kind of formatting that I was hoping for when the macro is done :-) One more thing. I can see that the formatting/macro also has some other issues. If you see the to date in column C for Steven in the input sheet, the to date is 2013.03.01 23:00 but in the Result sheet (see row 2545 in sheet 2) the to date is 2013.03.02 07:00 for some reason. Do you know why? :-)

Thank you so much guys for a great suggestions and fast work!!!!!! :-)

//Br Macenmin

**mehmetcik** · 04-26-2013, 01:52 PM

I am not seeing 25 hours in a day here.

I am adding two lines in the macro that will show that this is so. Delete between the two lines '******************* when you are satisfied that this is so.

I will also check the formatting

**mehmetcik** · 04-26-2013, 03:55 PM

Ok I have modified the formats a bit.

And found the 25 Hour Problem.

I don't really iunderstand the rest of your message and will address that now.

**mehmetcik** · 04-26-2013, 04:54 PM

This should meet your requirements.

**macenmin** · 05-03-2013, 05:08 AM

Awesome!!! Thank you so much, this is just what I wanted :-)

Can I please ask you or anybody else for just one more thing?

The second sheet now presents values on hourly basis pr day, allocated/splitted between the dates in sheet 1, pr person.
How can I instead of splitting this in to hourly values, get monthly values pr person?
I guess that I will have to do something with the formatting and the R1C1-formula but Im not really sure how...

Example:

Person: Alex
Date1: 01.01.2013
Date2: 01.04.2013
Value: 3000

The current macro that you have helped me with Mehmetcik is to split/devide the value into the sum of hours in the period, and allocate that sum in one row pr day and hr.

How can I get the result in sheet 2 to show me the result something like this:

Alex 01.2013 1000
Alex 02.2013 1000
Alex 03.2013 1000
...
...
and so on?

Thank you so much for very good help so far!!

Br,
Macenmin