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]

If you want to copy/paste values in columns Z-AG at the start, you can add a block like this to the top of the code (before your loop):

    Dim lr As Long
    
'   Find last row with data in column Z
    lr = Cells(Rows.Count, "Z").End(xlUp).Row
    
'   Turn all the formulas into values for columns Z-AG
    Range("Z2:AG" & lr).Value = Range("Z2:AG" & lr).Value

 

[kingspur06]

If you want to copy/paste values in columns Z-AG at the start, you can add a block like this to the top of the code (before your loop):

    Dim lr As Long
   
'   Find last row with data in column Z
    lr = Cells(Rows.Count, "Z").End(xlUp).Row
   
'   Turn all the formulas into values for columns Z-AG
    Range("Z2:AG" & lr).Value = Range("Z2:AG" & lr).Value

perfect - many thanks

 

[Joe4]

You are welcome.