Find a value and its position within a VBA code array variable

[krazykaj]

Hi all.

Some help again would be greatly appreciated

I have a variable which is an array of dates.
The amount of data varies i.e. it may have four dates stored in the variable, it may have 20 dates stored in the variable.

So for example let's say i have these six values stored as an array within the variable DateVariable()

1/1/2008, 1/2/2008, 1/3/2008, 1/4/2008, 1/5/2008, 1/6/2008

Now ... I want to be able to search that DateVariable() array for a certain date, and have returned to me the place of that date with in the array if it exists there.

So for example, say i want to search for the date '1/5/2008' within the DataVariable() array.
The search should return, using the above example, "5"

So basically, how do i search for a date value within an array ... to find first if that value is present in the array, and if it is, what position is in within the array.

Does that make sense?

Thank you heaps for you time and help
Cheers
KJ

 

[gauntletxg]

You can use MATCH

' Replace with your variable and however you fill the array
Dim DateVariable As Variant, iMatch As Integer
DateVariable = Array("1/1/2008", "1/2/2008", "1/3/2008", "1/4/2008", "1/5/2008", "1/6/2008")
On Error Resume Next
iMatch = WorksheetFunction.Match("1/2/2008", DateVariable, 0)
On Error GoTo 0
If iMatch > 0 Then
    ' Match
    Debug.Print iMatch
Else
    ' No match
End If

 

[krazykaj]

Awesome! Thanks.

As long as i kept things as text values for the dates ... it worked a treat!

Thanks again,
KJ

 

[Kenneth Hobson]

Of course Match is limited to the number of rows. To search by dates:

Sub test()
    ' Replace with your variable and however you fill the array
    Dim DateVariable As Variant, iMatch As Integer
    DateVariable = Array(#1/1/2008#, #1/2/2008#, #1/3/2008#, #1/4/2008#, #1/5/2008#, #1/6/2008#)
    On Error Resume Next
    iMatch = WorksheetFunction.Match(#1/2/2008#, DateVariable, 0)
    On Error GoTo 0
    If iMatch > 0 Then
        ' Match
        Debug.Print iMatch
    Else
        ' No match
    End If
End Sub

 

[ilya2004]

Thanks, this was helpful!