Hello,
I have a file with records which include transaction dates. I want to grab the last transaction date in every month and assign it as a statement date. I used an array function to implement this and it works. Below is a small sample that illustrates the idea.
![](/board/proxy.php?image=https%3A%2F%2Fi.ibb.co%2FqsVKzc8%2FUntitled.png&hash=c8e82ee4b24e15421d37bd92acee9dc8)
The formula in B2 is as follows: ={MAX(IF(MONTH($A$2:$A$5)=MONTH(A2),IF(YEAR($A$2:$A$5)=YEAR(A2),$A$2:$A$5,"")))}
I then used the array function in VBA to automate the process. Since array functions use a lot of memory, I ended up having memory issues with only about 2000 records.
The issues with my approach:
1. Using array functions
2. I don't need to use the whole column to determine the date of the last transaction in every month. The records are sorted, I only need to go up to the last date every month.
I think I should use a traditional loop in VBA to accomplish this. Any hint is appreciated.
I have a file with records which include transaction dates. I want to grab the last transaction date in every month and assign it as a statement date. I used an array function to implement this and it works. Below is a small sample that illustrates the idea.
![](/board/proxy.php?image=https%3A%2F%2Fi.ibb.co%2FqsVKzc8%2FUntitled.png&hash=c8e82ee4b24e15421d37bd92acee9dc8)
The formula in B2 is as follows: ={MAX(IF(MONTH($A$2:$A$5)=MONTH(A2),IF(YEAR($A$2:$A$5)=YEAR(A2),$A$2:$A$5,"")))}
I then used the array function in VBA to automate the process. Since array functions use a lot of memory, I ended up having memory issues with only about 2000 records.
The issues with my approach:
1. Using array functions
2. I don't need to use the whole column to determine the date of the last transaction in every month. The records are sorted, I only need to go up to the last date every month.
I think I should use a traditional loop in VBA to accomplish this. Any hint is appreciated.