Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macro to spread values between start and end date
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macro to spread values between start and end date
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 "Mark" wrote in message ... 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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macro to spread values between start and end date
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 "Mark" wrote in message ... 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Macro start and finish date from rows | Excel Worksheet Functions | |||
Calculating monthly start date with a start date | Excel Worksheet Functions | |||
Determining an annual review date from an employee start date | Excel Worksheet Functions | |||
Macro to find matching date and copy values to another sheet | Excel Discussion (Misc queries) | |||
formatting date-time values in macro | Excel Worksheet Functions |