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