Loading
Ozgrid Excel Help & Best Practices Forums

Excel Video Tutorials / 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,323

    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.
    MS MVP - Excel

  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,323

    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.
    MS MVP - Excel

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

    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,323

    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.
    MS MVP - Excel

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

    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,323

    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.
    MS MVP - Excel

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

    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,323

    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.
    MS MVP - Excel

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 HELP
    Replies: 1
    Last Post: February 6th, 2008, 08:39
  2. Check If Element Exists In Array
    By Lavner in forum EXCEL HELP
    Replies: 5
    Last Post: December 3rd, 2007, 16:39
  3. Check Sheet Already Exists And Add New Ones
    By tochybaja in forum EXCEL HELP
    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 HELP
    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