'Save Data to Array Variable in For Loop

I need to find the earliest & latest dates for certain table entries.

A link to a diagram of the table.

The premise is that every time an entry has a desired pairing of "Name" and "Desc" I want to grab the date, and then find the earliest & latest dates associated with that pairing.

My solution was to create an Array variable, save all the dates in integer form (Long) and then print the Min and Max functions of that Array.

The first date the loop encounters isn't saving to the array, so the first element is always "0" and there's always a date missing.

Sub Test2()
'Search a table for specied subject / entry description, to find the earliest & latest corresponding dates

'Empty array variable for later use
    Dim TheDates() As Long

    Dim Xds As String
    Dim Xnm As String
    Dim NameR As Range
    Dim Counter As Integer
    Dim Dum As Integer

'Placeholder values assigned to Xds and Xnm for testing
    Xds = "Charlie @ £8.50"
    Xnm = "Beatriz"

'Counter set to 0
    Counter = 0
    ReDim TheDates(Counter)

'Run through each entry in the table
    For Each NameR In Range("Draft[Name]")
'Check if an entry contains the desired pairing of Name & Desc ( Xnm & Xds )
        If NameR.Value = Xnm And NameR.Offset(0, 8).Value = Xds Then
'In cases they match follow the below procedure
    'Set the array size of 'TheDates' to current counter value
    'Set the 'counter'th element in the array to the entry's date
    'Increment the Counter ready for the next case
            ReDim TheDates(Counter)
            TheDates(Counter) = CDbl(NameR.Offset(0, 3))
            Counter = Counter + 1
        'When an entry does not match the desired pairing it is ignored
            Else
        End If
    Next

'For testing purpose I am printing the end results to the page
    Range("N13") = WorksheetFunction.Min(TheDates)
    Range("N14") = WorksheetFunction.Max(TheDates)

'I am also printing the array in its entirey to analyse
    For Dum = 0 To Counter - 1
    Range("Q13").Offset(Dum, 0) = TheDates(Dum)
    Next

End Sub


Sources

This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.

Source: Stack Overflow

Solution Source