VBA help needed please - splitting date range into monthly values!

[Liam61]

Hey guys,

First post here so please forgive me if I miss anything..

I am new to VBA and attempting to find some coding that will allow me to allocate the below section units between months based on course start and end dates.

I have entered a couple of lines below of what I'm working with. It is the values in bold that I would like to have automatically generated if possible, as this needs allocating over a 12 month period for over 5,000 course offerings.

The top row denotes the columns that are currently being used, but the dates currently run from Apr18 in column Q to Mar19 in column AB as shown.

H	I	J	N	V	W	X	Y	Z	AA	AB
Course Meet Date	Course End Date	Course Length (Days)	Section Units required	Sep18	Oct18	Nov18	Dec18	Jan19	Feb19	Mar19
7-Jan-19	31-Mar-19	84	2					0.6	0.67	0.74
4-Sep-18	14-Dec-18	102	20	5.29	6.08	5.88	2.75

<tbody>
</tbody>

If anyone is able to offer any help that would be greatly appreciated!!

Kind regards
Liam

 

[StephenCrump]

Welcome to the Forum!

Is there any particular reason you wanted to use VBA, e.g. perhaps as a coding exercise to develop your VBA skills?

If not, perhaps a formula approach:

Q2: =IFERROR(1/(1/(MAX(0,MIN(EDATE(Q$1,1),$I2+1)-MAX(Q$1,$H2))*$N2/$J2)),"")
(Assuming dates in Q1, R1 etc are 1 April 2018, 1 May 2018 etc)

Book1
	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V	W	X	Y	Z	AA	AB	AC
1	Start	End	Length				Units			Apr-18	May-18	Jun-18	Jul-18	Aug-18	Sep-18	Oct-18	Nov-18	Dec-18	Jan-19	Feb-19	Mar-19	Apr-19
2	7-Jan-19	31-Mar-19	84				2												0.60	0.67	0.74
3	4-Sep-18	14-Dec-18	102				20								5.29	6.08	5.88	2.75
Sheet1

 

[Liam61]

Stephen, you are a genius!

I think I was honestly overthinking this.. thanks so much!

Liam