total populate date

[Guest]

Hello All

My question is

I have a number of records with different start and end dates some start dates are in 03 and others are in 04 but I have a a figure eg. 1250 that needs to be populated throughout the months in 04

Start Date End Date Duration Total Jan Feb Mar Apr May Jun Jul ....Dec (all 04
02/01/03 02/01/04 2mths 04 1250 1250 1205
05/04/04 06/05/04 1mth 04 100 10

How do I get this to automatically populate the field without doing it manually. There are hundreds of records

Thank

JB

 

[Tom Ogilvy]

You have ambigous dates. Are your dates mm/dd/yy or dd/mm/yy.

Why does the second data row not have 1250 placed in it. ( 05/04/04
06/05/04 )

No matter what your date order, 05/04/04 06/05/04 touches two
months - What determines where the value goes in a situation like this.
First month, second month, both months? What are the rules?

In the first data row, you probably show Feb 1, 2004 as the end date, then
count that as 2 months in 04 and put in values in both Jan and Feb. In the
second data row, it is probably from May 4, 2004 to Jun 5, 2004 - but you
show 100 in March 2004. Again, what are the rules?


--
Regards,
Tom Ogilvy

Hello All,

My question is:

I have a number of records with different start and end dates some start

dates are in 03 and others are in 04 but I have a a figure eg. 1250 that
needs to be populated throughout the months in 04

Start Date End Date Duration Total Jan Feb Mar Apr May Jun Jul ....Dec (all 04)
02/01/03 02/01/04 2mths 04 1250 1250 1205
05/04/04 06/05/04 1mth 04 100 100


How do I get this to automatically populate the field without doing it

manually. There are hundreds of records.

 

[Bob Phillips]

JBP,

Does this do it


Sub PopulateFields()
Dim cLastRow As Long
Dim i As Long
Dim j As Long

For i = 2 To Cells(Rows.Count, "A").End(xlUp).Row
For j = 5 To 16
If (Cells(i, "A").Value <= Cells(1, j).Value And _
Cells(i, "B").Value >= Cells(1, j).Value) Then
Cells(i, j).Value = Cells(i, "D").Value
End If
Next j
Next i

End Sub


--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

Hello All,

My question is:

I have a number of records with different start and end dates some start

dates are in 03 and others are in 04 but I have a a figure eg. 1250 that
needs to be populated throughout the months in 04

Start Date End Date Duration Total Jan Feb Mar Apr May Jun Jul ....Dec (all 04)
02/01/03 02/01/04 2mths 04 1250 1250 1205
05/04/04 06/05/04 1mth 04 100 100


How do I get this to automatically populate the field without doing it

manually. There are hundreds of records.