Macro to create detailed payment schedule using summary list of bank facilities

[hbelal]

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.

[hbelal]

It seems that I posted my issue 3 times. Apologies but I didn't know how to edit it .

[hbelal]

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.

[Paul_Hossler]

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]

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