PDA

View Full Version : Macro to create detailed payment schedule using summary list of bank facilities



hbelal
11-18-2018, 10:13 PM
I have a table of bank facilities with different types and details (table called "Facility_Summary"). I am looking for your help to create a macro, which will create a detailed payment schedule based on the remaining payments of each applicable facility (Remaining PMTs) in a new worksheet using details included in the subject table following below logic or validation:

Step 1 - Create a new worksheet called "Schedule"
Step 2 - Within the "Facility_Summary" Table, validate each line item and apply the following logic: If facility type ="Credit Card" or "Overdraft", then skip and move to the next row,
Otherwise create number of rows based on number of "Remaining PMTs" in the the "Facility_Summary" Table with the following data:

Sr / Bank / Facility Type / EMI / Date

Important Note with regards to Date column: Macro will use "Next PMT Date" as point of reference for the first created detailed payment. Second detailed payment for the same facility (if applicable) will be created based on subject facility "Frequency" in the "Facility_Summary" Table (i.e. if frequency is monthly, then the date of second detailed payment shall be based on same reference date + 1 month).

I have attached along with this post a sample file with summary table and what I want to achieve from the requested macro.
I have a table of bank facilities with different types and details (table called "Facility_Summary"). I am looking for your help to create a macro, which will create a detailed payment schedule based on the remaining payments of each applicable facility (Remaining PMTs) in a new worksheet using details included in the subject table following below logic or validation:

Step 1 - Create a new worksheet called "Schedule"
Step 2 - Within the "Facility_Summary" Table, validate each line item and apply the following logic: If facility type ="Credit Card" or "Overdraft", then skip and move to the next row,
Otherwise create number of rows based on number of "Remaining PMTs" in the the "Facility_Summary" Table with the following data:

Sr / Bank / Facility Type / EMI / Date

Important Note with regards to Date column: Macro will use "Next PMT Date" as point of reference for the first created detailed payment. Second detailed payment for the same facility (if applicable) will be created based on subject facility "Frequency" in the "Facility_Summary" Table (i.e. if frequency is monthly, then the date of second detailed payment shall be based on same reference date + 1 month).

I have attached along with this post a sample file with summary table and what I want to achieve from the requested macro.
I have a table of bank facilities with different types and details (table called "Facility_Summary"). I am looking for your help to create a macro, which will create a detailed payment schedule based on the remaining payments of each applicable facility (Remaining PMTs) in a new worksheet using details included in the subject table following below logic or validation:

Step 1 - Create a new worksheet called "Schedule"
Step 2 - Within the "Facility_Summary" Table, validate each line item and apply the following logic: If facility type ="Credit Card" or "Overdraft", then skip and move to the next row,
Otherwise create number of rows based on number of "Remaining PMTs" in the the "Facility_Summary" Table with the following data:

Sr / Bank / Facility Type / EMI / Date

Important Notes with regards to Date column:
- Macro will use "Next PMT Date" as point of reference for the first created detailed payment. Second detailed payment for the same facility (if applicable) will be created based on subject facility "Frequency" in the "Facility_Summary" Table (i.e. if frequency is monthly, then the date of second detailed payment shall be based on same reference date + 1 month).
- For future dates, I don't require the accurate day. What is important for me is the accurate month and year as this is required for my analysis. Accordingly, I don't mind to make all future payments with day 15 for example.

Important Note with regards to number of remaining PMTs: I have added this column in my Summary sheet as a formula. It would be great if you can include it in your code in order not to confuse end user when collecting summary details. However, I don't mind keeping it as worksheet formula (As you will see in the attached example sheet)

I have attached along with this post a sample file with summary table and what I want to achieve from the requested macro. Thanks in advance. : pray2:

hbelal
11-19-2018, 03:51 AM
It seems that I posted my issue 3 times. Apologies but I didn't know how to edit it :banghead:.

hbelal
11-19-2018, 10:03 PM
Follow up guys, Here is the original post (as it is difficult to read it from above post).



I have a table of bank facilities with different types and details (table called "Facility_Summary"). I am looking for your help to create a macro, which will create a detailed payment schedule based on the remaining payments of each applicable facility (Remaining PMTs) in a new worksheet using details included in the subject table following below logic or validation:

Step 1 - Create a new worksheet called "Schedule"
Step 2 - Within the "Facility_Summary" Table, validate each line item and apply the following logic: If facility type ="Credit Card" or "Overdraft", then skip and move to the next row,
Otherwise create number of rows based on number of "Remaining PMTs" in the the "Facility_Summary" Table with the following data:

Sr / Bank / Facility Type / EMI / Date

Important Notes with regards to Date column:
- Macro will use "Next PMT Date" as point of reference for the first created detailed payment. Second detailed payment for the same facility (if applicable) will be created based on subject facility "Frequency" in the "Facility_Summary" Table (i.e. if frequency is monthly, then the date of second detailed payment shall be based on same reference date + 1 month).
- For future dates, I don't require the accurate day. What is important for me is the accurate month and year as this is required for my analysis. Accordingly, I don't mind to make all future payments with day 15 for example.

Important Note with regards to number of remaining PMTs: I have added this column in my Summary sheet as a formula. It would be great if you can include it in your code in order not to confuse end user when collecting summary details. However, I don't mind keeping it as worksheet formula (As you will see in the attached example sheet)

I have attached along with this post a sample file with summary table and what I want to achieve from the requested macro. Thanks in advance. : pray2:

Paul_Hossler
11-20-2018, 07:02 AM
Seems to me that your request is a LOT of work for anyone just using their free time and effort.

You might investigate using one of the paid consultants here or on other forums

hbelal
11-20-2018, 10:09 PM
Seems to me that your request is a LOT of work for anyone just using their free time and effort.

You might investigate using one of the paid consultants here or on other forums

Hi Paul. I appreciate your view and totally understand it. In all cases, I got the needed code and I will post it here for others reference in case something similar will be needed.



Option Explicit

Sub test()
Dim a, b, i As Long, ii As Long, iii As Long, Cols, n As Long, x
Cols = Array(1, 2, 3, 7, 8)
a = Sheets("summary").ListObjects(1).DataBodyRange.Value
ReDim b(1 To 100000, 1 To 5)
For i = 1 To UBound(a, 1)
If a(i, 3) <> "Credit Card" And a(i, 3) <> "Overdraft" Then
n = n + 1
For ii = 0 To UBound(Cols)
b(n, ii + 1) = a(i, Cols(ii))
Next
If a(i, 11) > 1 Then
x = GetInterval(a(i, 9))
If IsArray(x) Then
For ii = 1 To a(i, 11) - 1
n = n + 1
For iii = 0 To UBound(Cols) - 1
b(n, iii + 1) = a(i, Cols(iii))
Next
b(n, 5) = DateAdd(x(0), x(1) * ii, a(i, 8))
Next
End If
End If
End If
Next
If n = 0 Then Exit Sub
With Sheets.Add.Cells(1).Resize(, UBound(Cols) + 1)
.Value = Array("SI", "Bank", "Facility Type", "EMI", "Next PMT Date")
With .Rows(2).Resize(n)
.Value = b
.Columns(5).NumberFormat = "dd/mm/yyyy"
End With
.EntireColumn.AutoFit
.Parent.Name = "Schedule"
End With
End Sub


Function GetInterval(ByVal txt As String)
Select Case txt
Case "Monthly"
GetInterval = Array("m", 1)
Case "Quarterly"
GetInterval = Array("q", 3)
Case "Annual"
GetInterval = Array("yyyy", 12)
Case "Bi-Annual"
GetInterval = Array("m", 6)
End Select
End Function