Adding records to VBA Array based on Criteria

[heimdaloz]

Hi, I am trying to add a row of data for an excel sheet, for Example:

Date, Amount, Description, Statement, Entity

Into an array, where the date is greater than Start Date and less than End Date

The idea is to read a bank statement into an array and then either extract that data based on a date range or insert it into the array based on a date range, into my file.

 

[DanteAmor]

Try this:

Sub Add_Records_To_Array()
  Dim a As Variant, b As Variant
  Dim i As Long, j As Long, k As Long
  Dim StartDate As Date, EndDate As Date
 
  StartDate = "03/08/2024"
  EndDate = "25/08/2024"
 
  'We store all the data in array 'a', assuming that the data starts in cell A2.
  a = Range("A2:E" & Range("A" & Rows.Count).End(3).Row).Value
  ReDim b(1 To UBound(a, 1), 1 To UBound(a, 2))
 
  For i = 1 To UBound(a, 1)
  
    'The data that matches the dates is stored in the array 'b'.
    If a(i, 1) >= StartDate And a(i, 1) <= EndDate Then
      k = k + 1
      For j = 1 To UBound(a, 2)
        b(k, j) = a(i, j)
      Next
    End If
  Next

  'Then you can put the data on the sheet, for example, starting from cell K2:
  Range("K2").Resize(k, UBound(b, 2)).Value = b
End Sub