Splitting Data into Monthly Based on Date Range

K

kingspur06

Board Regular
Joined
Apr 24, 2007
Messages
52
Hi - I have a question relating to the attached thread

www.mrexcel.com

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 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
 

Excel Facts

Difference between two dates
Secret function! Use =DATEDIF(A2,B2,"Y")&" years"&=DATEDIF(A2,B2,"YM")&" months"&=DATEDIF(A2,B2,"MD")&" days"
Sort by date Sort by votes
When you get that error, if you hit the "Debug" button, which line of code does it highlight?
 
Upvote 0
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.
 
Upvote 0
Many thanks - here is the data

Book1.xlsm
ABCDEFGHIJKLMNOPQRSTUVWXYZAAABACADAEAFAG
1UIDDivisionRegionQuote ID OwnerClient NameDescriptionStageProbability Forecast ProbabilityForecast Probability Percentage Start Date Closed Date / End DateTotal Days CurrencyFXWeighted StatusRevenueProfitRevenue (Converted)Profit (Converted)Weighted Revenue (Converted)Weighted Profit (Converted)Weighted Balance Rev £Weighted Balance Profit £Daily RevenueDaily ProfitDaily Revenue (Converted)Daily Profit (Converted)Daily Weighted Revenue (Converted)Daily Weighted Profit (Converted)Daily Weighted Balance Rev £Daily Weighted Balance Profit £
21FSRUnited Kingdom9098JasonABC1DESCRIPTION1ACTIVE60Will Use6019/07/202101/08/202114GBP1Strong Visible20000500020000500072001800720018001428.571429357.14285711428.571429357.1428571514.2857143128.5714286514.2857143128.5714286
32FSRUnited Kingdom9165JasonABC2DESCRIPTION2ACTIVE60Will Use6026/07/202126/07/20211GBP1Strong Visible20000500020000500072001800720018002000050002000050007200180072001800
43FSRUnited Kingdom9213JasonABC5DESCRIPTION5ACTIVE60Will Use6002/08/202101/09/202131GBP1Strong Visible2000050002000050007200180072001800645.1612903161.2903226645.1612903161.2903226232.258064558.06451613232.258064558.06451613
54FSRUnited Kingdom9219JasonABC6DESCRIPTION6ACTIVE60Will Use6001/09/202120/11/202181GBP1Strong Visible2000050002000050007200180072001800246.913580261.72839506246.913580261.7283950688.8888888922.2222222288.8888888922.22222222
Sheet2
Cell Formulas
RangeFormula
Z2:AG5Z2=R2/$N2
V3,V5V3=((I3*K3)/10000)*T3
W3,W5W3=((I3*K3)/10000)*U3
 
Upvote 0
OK, it does start to do something for me on your last row.

But I think the first issue is with this line:
VBA Code: 
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.
VBA Code: 
If Cells(r, "M") >= WorksheetFunction.EoMonth(Cells(r, "L"), 0) + 1 Then

Secondly, all these rows look problematic:
VBA Code: 
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").
 
Upvote 0
Joe4 said:
OK, it does start to do something for me on your last row.

But I think the first issue is with this line:
VBA Code: 
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.
VBA Code: 
If Cells(r, "M") >= WorksheetFunction.EoMonth(Cells(r, "L"), 0) + 1 Then

Secondly, all these rows look problematic:
VBA Code: 
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").
Click to expand...
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!
 
Upvote 0
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
 
Upvote 0
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.
 
Upvote 0
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
 
Upvote 0
You must log in or register to reply here.

Similar threads

M
  • Question Question
Formulas with Inititals with Last Name
Replies
3
Views
98
Fluff
Fluff
zookeepertx
  • Question Question
Having trouble "deciphering" some code so I can see how it works
Replies
7
Views
372
zookeepertx
zookeepertx
K
  • Solved
Previously working very complex VBA now stopped
Replies
7
Views
532
Alex Blakenburg
A
K
  • Solved
Using VBA to select a color coded cell
2
Replies
11
Views
933
Alex Blakenburg
A
K
  • Solved
Update to existing Move data macro
2
Replies
11
Views
732
Alex Blakenburg
A

Forum statistics

Threads
1,223,903
Messages
6,175,284
Members
452,630
Latest member
OdubiYouth

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top