Log In
Register
Home
New Member
September 13, 2019
Offline
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.
VIP
Trusted Members
June 25, 2016
Offline
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
1 Guest(s)
