Loading
Ozgrid Excel Help & Best Practices Forums

Excel Training / Excel Dashboards Reports



Page 1 of 2 1 2 LastLast
Results 1 to 10 of 16

Thread: Check If Value Exists In an Array

  1. #1
    Join Date
    10th September 2005
    Posts
    2

    Check If Value Exists In an Array

    Is there a way that I can check for the occurance of a specific value in a collection object like an Array or a Range in excel through VBA code?

    For example I would like to check programatically if the value "orange" is present in an array by name Fruits(), where the array Fruits (3)=("mango", "banana", "apple").

    Please help.

    Excel Video Tutorials / Excel Dashboards Reports


  2. #2
    Join Date
    12th September 2006
    Location
    The Great State of Texas
    Posts
    4,329

    Re: Check A Specific Value In A Collection Object Vba

    Welcome to Oz, tk

    In a Range, you can use the Find method (analogous to the Find you do in the user interface with Cntl+F), for which you will find hundreds of examples here on Oz. Do a forum search.

    For an array, you have to traverse the elements in a loop.

    "Collection" has a specific meaning in VBA, and a Collection object can include anything (ranges, arrays, numbers, strings, ...), but it is a separate object altogether. Its members can be located via their keys.
    Entia non sunt multiplicanda sine necessitate.

  3. #3
    Join Date
    21st October 2006
    Location
    Czech Republic
    Posts
    40

    Re: Check A Specific Value In A Collection Object Vba

    Or

    you can use this code

    VB:
    Dim fruits() As Variant 
     'EVER Option BASE 0 and type of String !!
    Dim FindOutArray() As String 
    Const strText As String = "orange" 
    fruits = Array("mango", "banana", "apple") 
     
     ' filter choose item from array fruits
    FindOutArray = Filter(SourceArray:=fruits, _ 
    Match:=strText, _ 
    Include:=True, _ 
    Compare:=vbTextCompare) 
     
    If UBound(FindOutArray) = -1 Then 
        MsgBox "No, Array doesn't contain this item - " & strText 
    Else 
        MsgBox "Yes, Array contains this item - " & strText 
    End If 
    
    
    but attention
    notice parameter
    VB:
    strText 
    
    
    VB:
    Dim fruits() As Variant 
     'EVER Option BASE 0 and type of String !!
    Dim FindOutArray() As String 
    Const strText As String = "an" 
    fruits = Array("mango", "banana", "apple") 
     
     ' filter choose item from array fruits
    FindOutArray = Filter(SourceArray:=fruits, _ 
    Match:=strText, _ 
    Include:=True, _ 
    Compare:=vbTextCompare) 
     
    If UBound(FindOutArray) = -1 Then 
        MsgBox "No, Array doesn't contain this item - " & strText 
    Else 
        For i = 0 To UBound(FindOutArray) 
            MsgBox "Yes, Array contains this item - " & strText & vbCrLf & _ 
            "Found this item (Total " & UBound(FindOutArray) + 1 & " items ) " & FindOutArray(i), vbInformation, _ 
            "Total found items - " & UBound(FindOutArray) + 1 
        Next i 
    End If 
    
    

    Excel Video Tutorials / Excel Dashboards Reports


  4. #4
    Join Date
    12th September 2006
    Location
    The Great State of Texas
    Posts
    4,329

    Re: Check A Specific Value In A Collection Object Vba

    Very nice, PetLahev, I've never used the Filter function in VBA.
    Entia non sunt multiplicanda sine necessitate.

  5. #5
    Join Date
    23rd April 2007
    Posts
    3,892

    Re: Check A Specific Value In A Collection Object Vba

    For arrays, you could use MATCH. As in
    VB:
    MsgBox Application.Match("y", Array("a", "y", 3), 0) 
    : Rem returns 2, even though Array() is 0 based.
    
    
    It fails is the array is too large.
    Last edited by shg; September 10th, 2007 at 07:02.

    Excel Video Tutorials / Excel Dashboards Reports


  6. #6
    Join Date
    12th September 2006
    Location
    The Great State of Texas
    Posts
    4,329

    Re: Check A Specific Value In A Collection Object Vba

    It fails is the array is too large.
    How large, Mike?
    Entia non sunt multiplicanda sine necessitate.

  7. #7
    Join Date
    23rd April 2007
    Posts
    3,892

    Re: Check A Specific Value In A Collection Object Vba

    It seems that an array of 4095 elements works and 4096 elements genenrates a Type Mismatch error. I'm not sure if this limitation is platform dependant or not. In practice, I assume that spreadsheet/array functions (eg.INDEX(array,0,1) to extract a column) fail at about 3500 total elements.

    P.S. 4096 = 256*16 , which is 1/8 of the way to the end of "integer"

    Excel Video Tutorials / Excel Dashboards Reports


  8. #8
    Join Date
    12th September 2006
    Location
    The Great State of Texas
    Posts
    4,329

    Re: Check A Specific Value In A Collection Object Vba

    Works thru 65536 rows in Excel 2003.

    Edit: OK, so I was curious:
    VB:
    Sub x() 
        Const n As Long = 65537 
        Dim al(1 To n) As Long, i As Long, j As Long 
         
        For i = 1 To n 
            al(i) = i 
        Next 
         
        For i = 1 To 100 
            j = Rnd * n + 1 
            Debug.Print j, WorksheetFunction.Match(j, al, 0) 
        Next 
    End Sub 
    
    
    It falls over when n > 65536.
    Entia non sunt multiplicanda sine necessitate.

  9. #9
    Join Date
    23rd April 2007
    Posts
    3,892

    Re: Check A Specific Value In A Collection Object Vba

    On my Mac (Excel 2004), the first msgbox always works, but the last three lines throw errors at 4096 rows in myRange.

    VB:
    Dim myRange As Range 
    Dim myArray As Variant 
    Set myRange = Range("a1:a65536") 
     
     MsgBox Application.Match(20, myRange, 0): Rem works
     
    myArray = myRange.Value 
     MsgBox Application.Match(20, myArray, 0): Rem fails at high values
     
    myArray = Application.Transpose(myRange.Value) 
    MsgBox Application.Match(20, myArray, 0) 
    
    

    Excel Video Tutorials / Excel Dashboards Reports


  10. #10
    Join Date
    12th September 2006
    Location
    The Great State of Texas
    Posts
    4,329

    Re: Check A Specific Value In A Collection Object Vba

    Curious, considering Office 2004 for Mac presumably came after office 2003 for PC ...
    Entia non sunt multiplicanda sine necessitate.

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Possible Answers

  1. Check If Directory Exists
    By cbr600rr2006 in forum Excel General
    Replies: 1
    Last Post: February 6th, 2008, 09:39
  2. Check If Element Exists In Array
    By Lavner in forum Excel General
    Replies: 5
    Last Post: December 3rd, 2007, 17:39
  3. Check Sheet Already Exists And Add New Ones
    By tochybaja in forum Excel General
    Replies: 3
    Last Post: April 20th, 2007, 08:23
  4. Check If A Table Exists
    By SerenityNetwork in forum Excel and/or Access Help
    Replies: 3
    Last Post: October 14th, 2006, 23:24
  5. Check if Worksheet Exists
    By hu66666 in forum Excel General
    Replies: 6
    Last Post: August 15th, 2005, 00:58

Bookmarks

Posting Permissions

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