'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 |
---|