Is there a simple function, similar to Intersect for ranges, which determines whether a value is contained in an array?
MD
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.
Enjoy,
Tony
---------------------------------------------------------------
Give a man a fish and he'll eat for a day.
Teach him how to fish and he'll sit in a boat and drink beer all day.
I'm (slowly) building my own site: www.WordArticles.com
Thought as much!
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)
Windows 2k, Excel 2002 SP3
It's an example of how to use both.
Originally Posted by brettdj
Duhhh....
Windows 2k, Excel 2002 SP3
LOL