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:
Powered by vBulletin® Version 4.2.5 Copyright © 2024 vBulletin Solutions Inc. All rights reserved.