Splitting Data into Monthly Based on Date Range

[kingspur06]

Hi - I have a question relating to the attached thread

Splitting Data into Months based on Date Range

I have been doing some research but was unable to find exactly what I am trying to achieve. I have been working with VBA frequently but am not quite advanced enough yet to do do this, so I would really appreciate some help! I have a list of customer bookings, with various information and a date...

www.mrexcel.com

I know nothing about VBA so have been trying to update the code that was suggested on that thread to fit my data.
My ID is in Col A
My Start Date is Col L, End Date is Col M, Total Days is Col N.
I then have 8 separate Total Values from Cols R-Y and calculated Daily Values from Cols Z-AG.

So using the attached code, I tweaked it to this.....

Sub SplitBookings()
Dim r As Long

Application.ScreenUpdating = False
r = 2
While Cells(r, "A") <> ""
If Cells(r, "M") > WorksheetFunction.EoMonth(Cells(r, "L"), 0) + 1 Then
Rows(r + 1).Insert
Rows(r).Copy Rows(r + 1)
Cells(r, "M") = WorksheetFunction.EoMonth(Cells(r, "L"), 0) + 1
Cells(r + 1, "L") = Cells(r, "M")
Cells(r, "N") = Cells(r, "M") - Cells(r, "L")
Cells(r, "R") = Cells(r, "N") * Cells(r, "Z")
Cells(r, "S") = Cells(r, "N") * Cells(r, "AA")
Cells(r, "T") = Cells(r, "N") * Cells(r, "AB")
Cells(r, "U") = Cells(r, "N") * Cells(r, "AC")
Cells(r, "V") = Cells(r, "N") * Cells(r, "AD")
Cells(r, "W") = Cells(r, "N") * Cells(r, "AE")
Cells(r, "X") = Cells(r, "N") * Cells(r, "AF")
Cells(r, "Y") = Cells(r, "N") * Cells(r, "AG")
Cells(r + 1, "N") = Cells(r + 1, "M") - Cells(r + 1, "L")
Cells(r + 1, "R") = Cells(r + 1, "H") * Cells(r + 1, "Z")
Cells(r + 1, "S") = Cells(r + 1, "H") * Cells(r + 1, "AA")
Cells(r + 1, "T") = Cells(r + 1, "H") * Cells(r + 1, "AB")
Cells(r + 1, "U") = Cells(r + 1, "H") * Cells(r + 1, "AC")
Cells(r + 1, "V") = Cells(r + 1, "H") * Cells(r + 1, "AD")
Cells(r + 1, "W") = Cells(r + 1, "H") * Cells(r + 1, "AE")
Cells(r + 1, "X") = Cells(r + 1, "H") * Cells(r + 1, "AF")
Cells(r + 1, "Y") = Cells(r + 1, "H") * Cells(r + 1, "AG")
End If
r = r + 1
Wend
Application.ScreenUpdating = True

End Sub

However I get a Run-time error '13': Type mismatch when I try to run it.

Wondered if there is anybody that actually knows VBA that could let me know what I have done wrong?
many thanks
Mark

 

[Joe4]

When you get that error, if you hit the "Debug" button, which line of code does it highlight?

 

[kingspur06]

When you get that error, if you hit the "Debug" button, which line of code does it highlight?

For some reason when I run the code again I do not get that error anymore....but nothing updates

 

[Joe4]

If you post a sample of your data in a format that we can easily copy, we can try running your code against your data to see what it is doing.

MrExcel has a tool called “XL2BB” that lets you post samples of your data that will allow us to copy/paste it to our Excel spreadsheets, so we can work with the same copy of data that you are. Instructions on using this tool can be found here: XL2BB Add-in

Note that there is also a "Test Here” forum on this board. This is a place where you can test using this tool (or any other posting techniques that you want to test) before trying to use those tools in your actual posts.

 

[kingspur06]

Many thanks - here is the data

Book1.xlsm
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V	W	X	Y	Z	AA	AB	AC	AD	AE	AF	AG
1	UID	Division	Region	Quote ID	Owner	Client Name	Description	Stage	Probability	Forecast Probability	Forecast Probability Percentage	Start Date	Closed Date / End Date	Total Days	Currency	FX	Weighted Status	Revenue	Profit	Revenue (Converted)	Profit (Converted)	Weighted Revenue (Converted)	Weighted Profit (Converted)	Weighted Balance Rev £	Weighted Balance Profit £	Daily Revenue	Daily Profit	Daily Revenue (Converted)	Daily Profit (Converted)	Daily Weighted Revenue (Converted)	Daily Weighted Profit (Converted)	Daily Weighted Balance Rev £	Daily Weighted Balance Profit £
2	1	FSR	United Kingdom	9098	Jason	ABC1	DESCRIPTION1	ACTIVE	60	Will Use	60	19/07/2021	01/08/2021	14	GBP	1	Strong Visible	20000	5000	20000	5000	7200	1800	7200	1800	1428.571429	357.1428571	1428.571429	357.1428571	514.2857143	128.5714286	514.2857143	128.5714286
3	2	FSR	United Kingdom	9165	Jason	ABC2	DESCRIPTION2	ACTIVE	60	Will Use	60	26/07/2021	26/07/2021	1	GBP	1	Strong Visible	20000	5000	20000	5000	7200	1800	7200	1800	20000	5000	20000	5000	7200	1800	7200	1800
4	3	FSR	United Kingdom	9213	Jason	ABC5	DESCRIPTION5	ACTIVE	60	Will Use	60	02/08/2021	01/09/2021	31	GBP	1	Strong Visible	20000	5000	20000	5000	7200	1800	7200	1800	645.1612903	161.2903226	645.1612903	161.2903226	232.2580645	58.06451613	232.2580645	58.06451613
5	4	FSR	United Kingdom	9219	Jason	ABC6	DESCRIPTION6	ACTIVE	60	Will Use	60	01/09/2021	20/11/2021	81	GBP	1	Strong Visible	20000	5000	20000	5000	7200	1800	7200	1800	246.9135802	61.72839506	246.9135802	61.72839506	88.88888889	22.22222222	88.88888889	22.22222222
Sheet2

Cell Formulas
Range	Formula
Z2:AG5	Z2	=R2/$N2
V3,V5	V3	=((I3*K3)/10000)*T3
W3,W5	W3	=((I3*K3)/10000)*U3

 

[Joe4]

OK, it does start to do something for me on your last row.

But I think the first issue is with this line:

If Cells(r, "M") > WorksheetFunction.EoMonth(Cells(r, "L"), 0) + 1 Then

I think that needs to be ">=" instead of ">", or else it does not consider the first day of the next month to need a new row, i.e.

If Cells(r, "M") >= WorksheetFunction.EoMonth(Cells(r, "L"), 0) + 1 Then

Secondly, all these rows look problematic:

Cells(r + 1, "R") = Cells(r + 1, "H") * Cells(r + 1, "Z")
Cells(r + 1, "S") = Cells(r + 1, "H") * Cells(r + 1, "AA")
Cells(r + 1, "T") = Cells(r + 1, "H") * Cells(r + 1, "AB")
Cells(r + 1, "U") = Cells(r + 1, "H") * Cells(r + 1, "AC")
Cells(r + 1, "V") = Cells(r + 1, "H") * Cells(r + 1, "AD")
Cells(r + 1, "W") = Cells(r + 1, "H") * Cells(r + 1, "AE")
Cells(r + 1, "X") = Cells(r + 1, "H") * Cells(r + 1, "AF")
Cells(r + 1, "Y") = Cells(r + 1, "H") * Cells(r + 1, "AG")

as you are trying to multiply by column "H", but you cannot since it is a text entry ("ACTIVE").

 

[kingspur06]

OK, it does start to do something for me on your last row.

But I think the first issue is with this line:

If Cells(r, "M") > WorksheetFunction.EoMonth(Cells(r, "L"), 0) + 1 Then

I think that needs to be ">=" instead of ">", or else it does not consider the first day of the next month to need a new row, i.e.

If Cells(r, "M") >= WorksheetFunction.EoMonth(Cells(r, "L"), 0) + 1 Then

Secondly, all these rows look problematic:

Cells(r + 1, "R") = Cells(r + 1, "H") * Cells(r + 1, "Z")
Cells(r + 1, "S") = Cells(r + 1, "H") * Cells(r + 1, "AA")
Cells(r + 1, "T") = Cells(r + 1, "H") * Cells(r + 1, "AB")
Cells(r + 1, "U") = Cells(r + 1, "H") * Cells(r + 1, "AC")
Cells(r + 1, "V") = Cells(r + 1, "H") * Cells(r + 1, "AD")
Cells(r + 1, "W") = Cells(r + 1, "H") * Cells(r + 1, "AE")
Cells(r + 1, "X") = Cells(r + 1, "H") * Cells(r + 1, "AF")
Cells(r + 1, "Y") = Cells(r + 1, "H") * Cells(r + 1, "AG")

as you are trying to multiply by column "H", but you cannot since it is a text entry ("ACTIVE").

many thanks....I will give that a go.
And I can see why those lines are problematic now. It should not be multiplying by column "H". This was the column from the original thread. It should be "N". my bad!

 

[kingspur06]

OK so I have made some tweaks and the dates and Total Days columns now work perfectly. But I cannot figure out why the totals from R to Y are not updating.
Here is my updated code......there is clearly something wrong with the lines I have marked in red but it looks to be the same equation happening that is working well in the line above it in green.....so any other suggestions much appreciated!

Sub SplitBookings()
Dim r As Long

Application.ScreenUpdating = False
r = 2
While Cells(r, "A") <> ""
If Cells(r, "M") >= WorksheetFunction.EoMonth(Cells(r, "L"), 0) + 1 Then
Rows(r + 1).Insert
Rows(r).Copy Rows(r + 1)
Cells(r, "M") = WorksheetFunction.EoMonth(Cells(r, "L"), 0)
Cells(r + 1, "L") = Cells(r, "M") + 1
Cells(r, "N") = Cells(r, "M") - Cells(r, "L") + 1
Cells(r, "R") = Cells(r, "N") * Cells(r, "Z")
Cells(r, "S") = Cells(r, "N") * Cells(r, "AA")
Cells(r, "T") = Cells(r, "N") * Cells(r, "AB")
Cells(r, "U") = Cells(r, "N") * Cells(r, "AC")
Cells(r, "V") = Cells(r, "N") * Cells(r, "AD")
Cells(r, "W") = Cells(r, "N") * Cells(r, "AE")
Cells(r, "X") = Cells(r, "N") * Cells(r, "AF")
Cells(r, "Y") = Cells(r, "N") * Cells(r, "AG")
Cells(r + 1, "N") = Cells(r + 1, "M") - Cells(r + 1, "L") + 1
Cells(r + 1, "R") = Cells(r + 1, "N") * Cells(r + 1, "Z")
Cells(r + 1, "S") = Cells(r + 1, "N") * Cells(r + 1, "AA")
Cells(r + 1, "T") = Cells(r + 1, "N") * Cells(r + 1, "AB")
Cells(r + 1, "U") = Cells(r + 1, "N") * Cells(r + 1, "AC")
Cells(r + 1, "V") = Cells(r + 1, "N") * Cells(r + 1, "AD")
Cells(r + 1, "W") = Cells(r + 1, "N") * Cells(r + 1, "AE")
Cells(r + 1, "X") = Cells(r + 1, "N") * Cells(r + 1, "AF")
Cells(r + 1, "Y") = Cells(r + 1, "N") * Cells(r + 1, "AG")
End If
r = r + 1
Wend
Application.ScreenUpdating = True

End Sub

 

[Joe4]

I am not at my work computer where I set this all up right now (I will be back tomorrow).

In these cases, I find that one of the most helpful tools is "stepping through your code line-by-line and watching what happens on the screen".
If you have two monitors, this is really easy to do, as you show the worksheet on one screen and the VBA code editor on the other.
If you only have one screen, then you can split it to show both.

Then, step through your code one line at a time as you watch what happens on your sheet.
You can also hover over any variable in the VBA code to see what its value is at that point in time.
Many times when you do this and can see exactly what is happening, the issues become evident.

Here is a link that shows you how to do that: Debugging in Excel VBA
Don't worry about the Command Button part. Just go to the part about how to step through the code.

 

[kingspur06]

Playing around with this a little more and I think I have found my remaining issue. Nothing wrong with the code. The issue is my daily rate cells Z-AG are calculated based on the total values in R-Y. So everytime the code changed the number of days, it was changing the daily rate which keeps the total rate unchanged. Solution is to Copy Paste Values only into the columns Z-AG.
I can do that manually of course....but is there a line of code that I can add at the start that can form part of the macro?

Thank you everyone for all your help on this