Solved: Check if value is in an array

Is there a simple function, similar to Intersect for ranges, which determines whether a value is contained in an array?
MD

In a Word - No !!

Sorry. You can arrange it with Collections, but not Arrays.

Thought as much! :mkay

You can loop through the array really quickly though.

[vba]
Option Explicit

Sub Test()

Dim MyArray() As String
Dim n As Long
Dim i As Long
Dim IsThere As Boolean
Dim FindText As String

n = Range("A65536").End(xlUp).Row
ReDim MyArray(1 To n)
For i = 1 To n
MyArray(i) = Range("A" & i).Text
Next i

FindText = "test"
For i = 1 To n
If MyArray(i) = FindText Then
IsThere = True
Exit For
End If
Next i

If IsThere = True Then
MsgBox "I found it.", vbInformation
Else
MsgBox "I looked everywhere, but it just isn't there.", vbInformation
End If

End Sub
[/vba]

Hi Malcolm,

Yes it is straightforward, you can use the worksheet function MATCH ie

[vba]
Sub TestArray()
Dim X, YourNum As Integer, NumThere As Boolean, NumPos As Long
X = Range("A:A")
YourNum = 10
On Error Resume Next
NumPos = Application.WorksheetFunction.Match(YourNum, X, 0)
If NumPos > 0 Then NumThere = True
On Error GoTo 0
If NumThere Then
MsgBox "The number " & YourNum & " is in position " & NumPos
Else
MsgBox "The number " & YourNum & " is not in the array"
End If
End Sub
[/vba]

Cheers

Dave

Or (playing devils advocate) you could use scripting.dictionary - my favourite class of the month! I'll never go back to collections again...

Hi Maclean,
Not something I've ever used. Can you elaborate?
MD

There very similar to collections in functionality - just a few less lines to set up - but they have one great advantage (for me anyway) in that they have an "Exists" method - which means you dont have to catch errors - which is sooo slow.


http://msdn.microsoft.com/library/de...Dictionary.asp

Malcolm,

See the code to deal a poker hand in the kb article http://www.vbaexpress.com/kb/getarticle.php?kb_id=67, it uses both a dictionary and a collection

I also used the dictionary as the backbone of my Duplicate Master addin

Cheers

Dave

Hi Brettdj

Whats the reason for using both of them (just curious)

It's an example of how to use both.

Quote:

---

Originally Posted by brettdj

The code was written in two ways: as an example of how to use the (VBA) Dictionary Object or a VBA Collection to remove an item (dealt card) from a collection (pack of cards).

---

Duhhh.... :oops:

LOL
:rotlaugh: