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

K

krazykaj

Board Regular
Joined
Jul 20, 2005
Messages
143
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
 

Excel Facts

Remove leading & trailing spaces
Save as CSV to remove all leading and trailing spaces. It is faster than using TRIM().
Sort by date Sort by votes
You can use MATCH

Code: 
' 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
 
Last edited:
Upvote 0
Awesome! Thanks.

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

Thanks again,
KJ
 
Upvote 0
Of course Match is limited to the number of rows. To search by dates:
Code: 
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
 
Upvote 0
You must log in or register to reply here.

Similar threads

J
  • Question Question
Group last name based on range in alphabet
Replies
2
Views
174
Peter_SSs
Peter_SSs
J
  • Question Question
Multiple Layer VLOOKUP, or better alternative?
Replies
9
Views
625
JukeExcel
J
J
  • Solved
Search/Filter a Spreadsheet by User Entered Value
Replies
3
Views
201
jbennett01
J
U
  • Solved
ADDING 2ND COLUMN TO ARRAY, THEN ONLY OUTPUTTING THE SECOND COLUMN
Replies
3
Views
288
StephenCrump
S
J
  • Solved
VBA to Search and Extract 7 Values from a Single Cell
Replies
1
Views
175
Jeffrey Mahoney
Jeffrey Mahoney

Forum statistics

Threads
1,224,585
Messages
6,179,703
Members
452,938
Latest member
babeneker

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top