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.


[TABLE="width: 700"]
<tbody>[TR]
[TD]H[/TD]
[TD]I[/TD]
[TD]J[/TD]
[TD]N[/TD]
[TD]V[/TD]
[TD]W[/TD]
[TD]X[/TD]
[TD]Y[/TD]
[TD]Z[/TD]
[TD]AA[/TD]
[TD]AB[/TD]
[/TR]
[TR]
[TD]Course Meet Date[/TD]
[TD]Course End Date[/TD]
[TD]Course Length (Days)[/TD]
[TD]Section Units required[/TD]
[TD]Sep18[/TD]
[TD]Oct18[/TD]
[TD]Nov18[/TD]
[TD]Dec18[/TD]
[TD]Jan19[/TD]
[TD]Feb19[/TD]
[TD]Mar19[/TD]
[/TR]
[TR]
[TD]7-Jan-19[/TD]
[TD]31-Mar-19[/TD]
[TD]84[/TD]
[TD]2[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]0.6[/TD]
[TD]0.67[/TD]
[TD]0.74[/TD]
[/TR]
[TR]
[TD]4-Sep-18[/TD]
[TD]14-Dec-18[/TD]
[TD]102[/TD]
[TD]20[/TD]
[TD]5.29[/TD]
[TD]6.08[/TD]
[TD]5.88[/TD]
[TD]2.75[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

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