Macro to spread values between start and end date

[Mark]

I have got a spreadsheet which has start dates in column A, end dates in
column B, and the value to be spread between them in column C. The dates
have been converted to weeknumbers using weeknum. Across the top of the
spreadsheet I have spread the week numbers 1 - 104 ( 2 years
worth ).Illustrated below:

Start End Value Weeks: 1 2 3 4 5 6 7 8 9
10 11 12
2 5 6
0 0 0
4 7 8

What I need to do is look at column A and B, look across the spreadsheet to
the corresponding weeknumbers in row 1 and spread the value in column c
between them (inclusive of the weeks in columns a and b) as shown below. I
have done this using formulae in a much smaller spreadsheet, however when I
try to do the same in this one it just falls over.

Start End Value Weeks: 1 2 3 4 5 6 7 8 9
10 11 12
2 5 6 6 6 6 6
0 0 0
4 7 8 8 8 8
8

The spreadsheet has approx. 9000 rows of data, in some of the rows, there
are no dates in columns a and b and they just need to be skipped over.

Any help would be much appreciated,

Thanks,

Mark

 

[Don Guillett]

try this
start end value wk1 wk2 wk3 wk4 wk5 wk6 wk7 wk8 wk9 wk10 wk11 wk12
10 11 12 12 12
2 5 6 6 6 6 6
0 0 0
4 7 8 8 8 8 8

=====using this
Sub putvalues()
For Each c In Range("a2:a5")'chg to suit
If c > 0 Then
startcol = c + 3
stopcol = c.Offset(, 1) + 3
Range(Cells(c.Row, startcol), Cells(c.Row, stopcol)) =c.Offset(, 2)
End If
Next
End Sub

 

[Doug Glancy]

Mark,

Here's a different take - slower than Don's I suspect. It requires you to
select the range to work with, and clears any cells that don't meet the
criteria (in case you are using the same sheet more than once):

Sub test()

Dim c As Range

For Each c In Selection
If Cells(1, c.Column) >= Cells(c.Row, 1) And Cells(1, c.Column) <=
Cells(c.Row, 2) Then
c.Value = Cells(c.Row, 3)
Else
c.ClearContents
End If
Next c

End Sub

hth,

Doug