Anbuselvam
Board Regular
- Joined
- May 10, 2017
- Messages
- 97
[FONT="]Hi[/FONT]
[FONT="]In the attached workbook [/FONT]
[FONT="]Sheet1, Cell D9 is the given date and the cell B12: H12 is the data range[/FONT]
[FONT="]Sheet2 A2: A15 is the list of dates where we need to lookup the given date or the closest date[/FONT]
[FONT="]In the list of dates in Sheet2, none is matching Sheet1, D9 dates of 13-Mar-19. So then It has to select the closest lesser dates of D9(13-Mar-19) which is (09-Mar-19)[/FONT]
[FONT="]Then the intersecting values of B2: H15 need to multiply by Sheet1 cell B12: H12 values and the results must be shown in D10 of Sheet 1. [/FONT]
The below link sheet I got a results by using SUMPRODUCT formula and to found the date I did not add the formulas.
https://www.dropbox.com/s/td4ggsbx7r1zbu3/Find closest date.xlsx?dl=0
[FONT="]If same Date present in the list then the same date intersecting values to multiply.
[/FONT][FONT="]I need a pure working VBA code to get the below results without using SUMPRODUCT, INDEX and MATCH Functions inside the code.
[/FONT]I want to learn VBA code as I have many similar formulas in other sheets to be changed in VBA Code
[FONT="]Hope someone will guide me to get the VBA Code.[/FONT]
[FONT="]In the attached workbook [/FONT]
[FONT="]Sheet1, Cell D9 is the given date and the cell B12: H12 is the data range[/FONT]
[FONT="]Sheet2 A2: A15 is the list of dates where we need to lookup the given date or the closest date[/FONT]
[FONT="]In the list of dates in Sheet2, none is matching Sheet1, D9 dates of 13-Mar-19. So then It has to select the closest lesser dates of D9(13-Mar-19) which is (09-Mar-19)[/FONT]
[FONT="]Then the intersecting values of B2: H15 need to multiply by Sheet1 cell B12: H12 values and the results must be shown in D10 of Sheet 1. [/FONT]
The below link sheet I got a results by using SUMPRODUCT formula and to found the date I did not add the formulas.
https://www.dropbox.com/s/td4ggsbx7r1zbu3/Find closest date.xlsx?dl=0
[FONT="]If same Date present in the list then the same date intersecting values to multiply.
[/FONT][FONT="]I need a pure working VBA code to get the below results without using SUMPRODUCT, INDEX and MATCH Functions inside the code.
[/FONT]I want to learn VBA code as I have many similar formulas in other sheets to be changed in VBA Code
[FONT="]Hope someone will guide me to get the VBA Code.[/FONT]