Announcement

Collapse
No announcement yet.

Check If Value Exists In an Array

Collapse
X
  • Filter
  • Time
  • Show
Clear All
new posts

  • 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.

  • #2
    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.

    Comment


    • #3
      Re: Check A Specific Value In A Collection Object Vba

      Or

      you can use this code

      Code:
      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
      Code:
      strText
      Code:
      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

      Comment


      • #4
        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.

        Comment


        • #5
          Re: Check A Specific Value In A Collection Object Vba

          For arrays, you could use MATCH. As in
          Code:
          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, 07:02.

          Comment


          • #6
            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.

            Comment


            • #7
              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"

              Comment


              • #8
                Re: Check A Specific Value In A Collection Object Vba

                Works thru 65536 rows in Excel 2003.

                Edit: OK, so I was curious:
                Code:
                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.

                Comment


                • #9
                  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.

                  Code:
                  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)

                  Comment


                  • #10
                    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.

                    Comment


                    • #11
                      Re: Check A Specific Value In A Collection Object Vba

                      There are a couple of things (like no ActiveX) that make 2004 different than 2003. Which lead to my suspision that the limitation was environment sensitive.

                      Comment


                      • #12
                        Re: Check A Specific Value In A Collection Object Vba

                        Thankyou very much shg, Mike and PetLahev. Your guidance was of great help.

                        Regards,
                        Manoj.

                        Comment


                        • #13
                          Re: Check A Specific Value In A Collection Object Vba

                          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").
                          Use the Filter Function

                          Comment


                          • #14
                            Re: Check A Specific Value In A Collection Object Vba

                            Originally posted by SHG
                            It falls over when n > 65536.
                            See Here
                            Reafidy

                            Forum Rules | Anatomy Of A Good Thread Title | What Have You Tried So Far? | Are You A Cross Poster?

                            Comment


                            • #15


                              Re: Check If Value Exists In an Array

                              Thanks Reafidy. My post was about limitations in using worksheet functions (well, one anyway -- Match) operating on declared arrays rather than ranges. I guess it's not surprising that the same limitations apply.
                              Entia non sunt multiplicanda sine necessitate.

                              Comment

                              Working...
                              X