Array Function VBA Out of Memory issue

[janpol]

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.


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.

 

[JustynaMK]

Maybe this will work for you (change Sheet name accordingly):

Sub foo()
    Dim lngLastRow          As Long
    Dim lngACount           As Long
    Dim lngBCount           As Long
    Dim lngMaxDate          As Long
    
    With [COLOR=#ff0000]Sheet2[/COLOR]
        lngLastRow = .Cells(.Rows.Count, "A").End(xlUp).Row
        For lngBCount = 2 To lngLastRow
            lngMaxDate = .Range("A" & lngBCount).Value
            For lngACount = 2 To lngLastRow
                If Month(.Range("A" & lngACount).Value) = Month(lngMaxDate) _
                    And Year(.Range("A" & lngACount).Value) = Year(lngMaxDate) _
                    And Day(.Range("A" & lngACount).Value) > Day(lngMaxDate) Then
                    lngMaxDate = .Range("A" & lngACount).Value
                End If
            Next lngACount
            .Range("B" & lngBCount).Value = lngMaxDate
        Next lngBCount
    End With
End Sub

 

[janpol]

Thank you so much! This works excellent!

 

[JustynaMK]

cool. Take care.