September 13, 2019
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:
' Spread_R7 Macro
' Allocate annual budget evenly across all periods
ActiveCell.FormulaR1C1 = "=R7C7/13"
Selection.AutoFill Destination:=Range("K7:W7"), Type:=xlFillDefault
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.
June 25, 2016
Give this a try.
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"
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.