Forum

Spreading a cell va...
 
Notifications
Clear all

Spreading a cell value evenly across a range of cells (eg, allocate annual to months)

3 Posts
2 Users
0 Reactions
184 Views
(@stevec-2)
Posts: 2
New Member
Topic starter
 

Dear People_Who_Know_More_Than_I

Can any of the gurus out there help me with a situation I have in an Excel worksheet?  I suspect it is a very basic question, but admit that so too are my skills with vba.

I want to take an annual budget figure and allocate it evenly across columns for each each month.  I want this to be triggered by the using press a button, because this allocation will not apply to all rows (only those where it is appropriate to allocate the budget evenly).  Because of this differing treatment for different rows, I think I cannot use regular formulae in the target (monthly) cells - thus trying to use vba.

So far I have manged to place buttons on my sheet and linked the following macro to a button:

Sub Spread_R7()
'
' Spread_R7 Macro
' Allocate annual budget evenly across all periods
''
Range("K7").Select
ActiveCell.FormulaR1C1 = "=R7C7/13"
Selection.AutoFill Destination:=Range("K7:W7"), Type:=xlFillDefault
Range("K7:W7").Select
End Sub

This seems to work just dandy for a single row (Row 7), but it would be pretty clunky to report this whole thing for every row.

So, I guess my question is, is it possible to do something 'dynamic' with the cell referencing so that a single macro can work on any row, perhaps taking its position from the row that the button sits in?

Any advice or assistance anyone can give will be gratefully received.

Thank you

SteveC.

 
Posted : 13/09/2019 9:54 pm
(@sunnykow)
Posts: 1417
Noble Member
 

Hi Stephen

Give this a try.

Sub SpreadAllocation()
Dim r As Long
Dim c As Long
Dim cell As Range

For Each cell In Selection
r = cell.Row
Range("K" & r).Select
For c = 11 To 23
Cells(r, c).Formula = "=G" & r & "/13"
Next
Next
End Sub

You can attach the macro to a single button.

Just select any cell in a row and run the macro.

It will work for multiple selection too.

Good luck

Sunny

 
Posted : 14/09/2019 12:28 am
(@stevec-2)
Posts: 2
New Member
Topic starter
 

Hi Sunny

Thank you very much for your reply and suggestion:  I shall give that a go.

Best regards

SteveC.

 
Posted : 16/09/2019 7:14 pm
Share: