PDA

View Full Version : Solved: Check if value is in an array

mdmackillop
10-17-2004, 07:46 AM
Is there a simple function, similar to Intersect for ranges, which determines whether a value is contained in an array?
MD

TonyJollans
10-17-2004, 08:02 AM
In a Word - No !!

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

mdmackillop
10-17-2004, 08:04 AM
Thought as much! :mkay

Jacob Hilderbrand
10-17-2004, 04:02 PM
You can loop through the array really quickly though.

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

brettdj
10-22-2004, 07:44 PM
Hi Malcolm,

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

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

Cheers

Dave

macleanb
02-09-2005, 01:02 AM
Or (playing devils advocate) you could use scripting.dictionary - my favourite class of the month! I'll never go back to collections again...

mdmackillop
02-09-2005, 01:33 AM
Hi Maclean,
Not something I've ever used. Can you elaborate?
MD

macleanb
02-09-2005, 01:46 AM
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/default.asp?url=/library/en-us/script56/html/jsobjDictionary.asp

brettdj
02-09-2005, 02:20 AM
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

macleanb
02-09-2005, 05:20 AM
Hi Brettdj

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

Jacob Hilderbrand
02-09-2005, 05:24 AM
It's an example of how to use both.

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).

macleanb
02-09-2005, 05:36 AM
Duhhh.... :oops:

brettdj
02-09-2005, 06:12 PM
LOL
:rotlaugh: