Consulting

Results 1 to 13 of 13

Thread: Solved: Check if value is in an array

  1. #1
    Administrator
    VP-Knowledge Base
    VBAX Grand Master mdmackillop's Avatar
    Joined
    May 2004
    Location
    Scotland
    Posts
    14,489
    Location

    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

  2. #2
    VBAX Master TonyJollans's Avatar
    Joined
    May 2004
    Location
    Norfolk, England
    Posts
    2,291
    Location
    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

  3. #3
    Administrator
    VP-Knowledge Base VBAX Grand Master mdmackillop's Avatar
    Joined
    May 2004
    Location
    Scotland
    Posts
    14,489
    Location
    Thought as much!

  4. #4
    Site Admin
    Jedi Master
    VBAX Guru Jacob Hilderbrand's Avatar
    Joined
    Jun 2004
    Location
    Roseville, CA
    Posts
    3,712
    Location
    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]

  5. #5
    Knowledge Base Approver VBAX Expert brettdj's Avatar
    Joined
    May 2004
    Location
    Melbourne
    Posts
    649
    Location
    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

  6. #6
    VBAX Regular macleanb's Avatar
    Joined
    Feb 2005
    Location
    Essex
    Posts
    23
    Location
    Or (playing devils advocate) you could use scripting.dictionary - my favourite class of the month! I'll never go back to collections again...

  7. #7
    Administrator
    VP-Knowledge Base VBAX Grand Master mdmackillop's Avatar
    Joined
    May 2004
    Location
    Scotland
    Posts
    14,489
    Location
    Hi Maclean,
    Not something I've ever used. Can you elaborate?
    MD

  8. #8
    VBAX Regular macleanb's Avatar
    Joined
    Feb 2005
    Location
    Essex
    Posts
    23
    Location
    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

  9. #9
    Knowledge Base Approver VBAX Expert brettdj's Avatar
    Joined
    May 2004
    Location
    Melbourne
    Posts
    649
    Location
    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

  10. #10
    VBAX Regular macleanb's Avatar
    Joined
    Feb 2005
    Location
    Essex
    Posts
    23
    Location
    Hi Brettdj

    Whats the reason for using both of them (just curious)
    Windows 2k, Excel 2002 SP3

  11. #11
    Site Admin
    Jedi Master
    VBAX Guru Jacob Hilderbrand's Avatar
    Joined
    Jun 2004
    Location
    Roseville, CA
    Posts
    3,712
    Location
    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).

  12. #12
    VBAX Regular macleanb's Avatar
    Joined
    Feb 2005
    Location
    Essex
    Posts
    23
    Location
    Duhhh....
    Windows 2k, Excel 2002 SP3

  13. #13
    Knowledge Base Approver VBAX Expert brettdj's Avatar
    Joined
    May 2004
    Location
    Melbourne
    Posts
    649
    Location
    LOL

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •