Macro to Spread Costs Evenly
Posted by Terry Bennett on August 28, 2001 10:48 PM
I am building a spreadsheet for departments to do their costs budgets in. It has a column for each month and a year total. I want to give users the option if they choose, to enter their full year budget in the Year Total column and have a macro button that will spread the costs evenly over the 12 months. I want them to be able able highlight the particular cells that should be spread. If they don't highlight a cell, the default position is that the Year Total column has a formula which sums the 12 months.
I don't know where to start with the macro and particularly how to deal with applying the macro to the selection.
Posted by Rudi on August 29, 2001 3:28 AM
Suggest that 2 buttons be used :-
(1) To split the Total evenly over 12 months
Sub Split_Over_12_Months()
With ActiveCell
If .Column <> 14 Then
MsgBox "To run this macro, you must select a cell in the Total column"
Exit Sub
End If
If MsgBox("Do you want the amount in " & .Address(False, False) & _
" split evenly over 12 months?", vbOKCancel) = vbCancel Then End
Range(.Offset(0, -12), .Offset(0, -1)).Value = .Value / 12
.FormulaR1C1 = "=SUM(RC[-12]:RC[-1])"
End With
End Sub
(2) To split the total evenly over selected months
Sub Split_Over_Selected_Months()
Dim rw As Integer
Dim area As Range
With Selection
rw = .Row
For Each area In .Areas
If rw <> area.Row Then
MsgBox "To run this macro, you must select cells in only one row."
Exit Sub
End If
Next
If .Cells.Count <> Intersect(Selection, Columns("B:M")).Cells.Count Then
MsgBox "To run this macro, you must select cells only in columns B:M"
Exit Sub
End If
With Cells(.Row, 14)
If MsgBox("Do you want the Total amount in " & _
.Address(False, False) & " split evenly over the selected cells?", _
vbOKCancel) = vbCancel Then End
.Copy
.PasteSpecial Paste:=xlValues
End With
Range(Cells(.Row, 2), Cells(.Row, 13)).ClearContents
.Value = Cells(.Row, 14) / .Cells.Count
Cells(.Row, 14).FormulaR1C1 = "=SUM(RC[-12]:RC[-1])"
End With
End Sub
The above are based on the assumptions that :
- the months are in columns 2 thru 13
- the Total is in column 14
