VBA Budgeting: Manually Enter Months or Evenly/Proportionally Spread

[chiggins1894]

Hi,
I am building an Excel template for my departments to complete for 2023 Budget. I'd like to give users two paths to enter their budget data:

1) Enter the monthly data manually (Column O-Z highlighted in light blue). AD3 would be "None" in this case as no VBA would need to be run.

or

2) Have them enter the total amount for the year in Column AC and give them the option to Spread "Evenly" (Total Year/12), Spread "Proportionally Based on PY Results" (based on results in B-N), or "None" referring to #1 above. In the attached file mock PY results are in columns B-N.

I'd like to be able to hit a macro button and all of the rows that are labeled as "Evenly" or "Proportionally Based on PY" would override the data within the monthly columns (Column O-Z). If the user manually enters the data and "None" is within Column AD I would like their data preserved and not overridden.

The attached file is just one row for simplicity purposes but I would replicate this for ~100 rows. Ideally it would be one Macro that would capture all rows. This would be a tremendous help and any insight would be appreciated!

[WideBoyDixon]

Hi there,

This does the calculations in memory so should be quick-ish on multiple rows:

Please Login or Register  to view this content.

WBD

[chiggins1894]

Thank you! This is very insightful. I'm realizing adapting this macro to my eventual template will run into some issues, but that's for me to learn/decipher line by line once I build something out.

I'm going to mark as solved. Thank you.