Using Ranges & Arrays to Compare A Series of Dates

[TopherC]

Hello Masters of Excel,

I am trying to write a procedure that will read a column of dates via a range, assign that range to an array of variants and then compare that array to another to see if it has expired.

I have success with reading the range to an array of variants but cannot get past this point. Every time I attempt to view, compare etc. the array of variants I get error code : 9 - which I am under the impression that I am "out of bounds." To corroborate my theory I have attempted to redimension my array to a specific bound (1 TO 10) but then lose all the data that was previously assigned from my range.

Here is the code:
*****************************************************
Sub LoopTest()


'{1} Define Variable Arrays for [1]Drilling Dates, [2]Expiration Dates & TRS, [2] NMA & TRS
Dim Drill_Date_Array() As Variant
Dim Expiration_Date() As Variant
Dim Expiration_TRS() As Variant
Dim NMA() As Variant
Dim NMA_TRS() As Variant
Dim Drill_Record() As Variant

'{2} Define Variables for temporary holding places for a range
Dim Temp_Array_A() As Variant
Dim Max_A As Integer
Dim Test_String As String
Dim Temp_Array_C() As Variant
Dim Temp_Array_D() As Variant
Dim Temp_Array_F() As Variant
Dim Temp_Array_G() As Variant
Dim Temp_Array_I() As Variant

'{3} Populate Variable Arrays at {1}
Drill_Date_Array = Range("A2:A8").Value
Expiration_Date = Range("C2:C8").Value
Expiration_TRS = Range("D2:D8").Value
NMA = Range("F2:F8").Value
NMA_TRS = Range("G2:G8")
Drill_Record = Range("I2:I8")

'{4} Print Variable Arrays (Initially)

Temp_Array_A = Drill_Date_Array
Range("A20:A30").Value = Temp_Array_A
'Max_A = ThisWorkbook.Names.Count
ReDim Temp_Array_A(1 To 10)

Temp_Array_C = Expiration_Date
Range("C20:C30") = Temp_Array_C

Temp_Array_D = Expiration_TRS
Range("D20:D30") = Temp_Array_D

Temp_Array_F = NMA
Range("F20:F30") = Temp_Array_F

Temp_Array_G = NMA_TRS
Range("G20:G30") = Temp_Array_G

Temp_Array_I = Drill_Record
Range("I20:I30") = Temp_Array_I


'{5} Test if Lease Expiration is within 21 days of 1st drill date to determine if expiration should be drilled
Dim i As Integer
Dim Message_Text As String
For i = LBound(Temp_Array_A) To UBound(Temp_Array_A)

Next i
'If DateValue(Temp_Array_A(1)) < DateValue(Temp_Array_C(1)) Then MsgBox ("Drill Date Less") Else: MsgBox ("Expiration Date Less")

End Sub
***********************************************

Any and all help is VERY MUCH APPRECIATED

Thank you!

 

[SydneyGeek]

Redim will completely refresh the array. If you want to keep the original contents use
Redim Preserve Temp_Array_A(1 to 10)

And another thing: Code is much easier to read if you use the CODE tags when you submit the post. If you're in Advanced mode you can use the # button. In Quick mode surround the code with [ CODE ] and [/ CODE ] (without the spaces).

Denis

 

[TopherC]

Thank you Denis!

I have done as you instructed with adding the preserve portion to the ReDim statement. I attempted to run through the for loop and display each of the values in the array with the MsgBox by assigning the element of the array at that iteration to a string and then message the string --- but am having little success. Ultimately I need to better learn how to manipulate arrays - can you please point me in the right direction? Thank you so much for your time and help.

Code below....

Sub LoopTest()


'{1} Define Variable Arrays for [1]Drilling Dates, [2]Expiration Dates & TRS, [2] NMA & TRS
        Dim Drill_Date_Array() As Variant
        Dim Expiration_Date() As Variant
        Dim Expiration_TRS() As Variant
        Dim NMA() As Variant
        Dim NMA_TRS() As Variant
        Dim Drill_Record() As Variant
        
'{2} Define Variables for temporary holding places for a range
        Dim Temp_Array_A() As Variant
        Dim Max_A As Integer
        Dim Test_String As String
        Dim Temp_Array_C() As Variant
        Dim Temp_Array_D() As Variant
        Dim Temp_Array_F() As Variant
        Dim Temp_Array_G() As Variant
        Dim Temp_Array_I() As Variant
            
'{3} Populate Variable Arrays at {1}
        Drill_Date_Array = Range("A2:A8").Value
        Expiration_Date = Range("C2:C8").Value
        Expiration_TRS = Range("D2:D8").Value
        NMA = Range("F2:F8").Value
        NMA_TRS = Range("G2:G8")
        Drill_Record = Range("I2:I8")

'{4} Print Variable Arrays (Initially)
        
        Temp_Array_A = Drill_Date_Array
        Range("A20:A30").Value = Temp_Array_A
        'Max_A = ThisWorkbook.Names.Count
        ReDim Preserve Temp_Array_A(1 To 10)
        
        Temp_Array_C = Expiration_Date
        Range("C20:C30") = Temp_Array_C
        
        Temp_Array_D = Expiration_TRS
        Range("D20:D30") = Temp_Array_D
        
        Temp_Array_F = NMA
        Range("F20:F30") = Temp_Array_F
        
        Temp_Array_G = NMA_TRS
        Range("G20:G30") = Temp_Array_G
        
        Temp_Array_I = Drill_Record
        Range("I20:I30") = Temp_Array_I
  
  
  '{5} Test if Lease Expiration is within 21 days of 1st drill date to determine if expiration should be drilled
        Dim i As Integer
        Dim Message_Text As String
        For i = LBound(Temp_Array_A) To UBound(Temp_Array_A)
            Message_Text = Temp_Array_A(i)
            MsgBox Message_Text
        Next i
        'If DateValue(Temp_Array_A(1)) < DateValue(Temp_Array_C(1)) Then MsgBox ("Drill Date Less") Else: MsgBox ("Expiration Date Less")
                        
End Sub

Thanks again,
Chris

 

[SydneyGeek]

Hi Chris, because you want to compare values you may find it easier to load everything onto a single array. Here's an example:

Sub OneArray()
    Dim arData()
    Dim i As Integer
    Dim lngRow As Long
    
    lngRow = 20
    arData = Range("A2:I8").Value
    
    For i = LBound(arData) To UBound(arData)
        If arData(i, 1) < arData(i, 3) Then
            Cells(lngRow, 1) = arData(i, 1)
            Cells(lngRow, 2) = arData(i, 3)
            Cells(lngRow, 3) = "Yes"
            lngRow = lngRow + 1
        Else
            Cells(lngRow, 1) = arData(i, 1)
            Cells(lngRow, 2) = arData(i, 3)
            Cells(lngRow, 3) = "No"
            lngRow = lngRow + 1
        End If
    Next i
End Sub

Note: LBound and UBound refere to the first and last rows of the array. the columns are numbered from 1 to 9 (in this case); regardless of the upper limit, the numbering is 1-based.

Also, if you pick up a single column array you need to transpose it to get the correct orientation in the final array. EG:

arDrill = WorksheetFunction.Transpose(Range("A2:A8"))

And when you pick up a single row, you transpose twice:

arData = WorksheetFunction.Transpose(WorksheetFunction.Transpose(Range("A2:J2")))

Denis