Announcement

Collapse
No announcement yet.

Check for presence of a value in an array

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

  • Check for presence of a value in an array



    Is there some built-in function that will quickly check to see if a particular value is in an array?

    For example, say I have an Array("1", "2", "3"), is there some boolean-returning function that would give me a false if I passed in that array and the number 4?

    If not, is there some easy way to implement this. Obviously you can use a loop to iterate through the array, checking each value, but that seems a little long.

    Thanks.

  • #2
    Re: Check for presence of a value in an array

    Sure there is. Use the Match function ...


    Code:
    Sub FooArrayFoo()
        Dim Arr() As Variant
        Dim lngLoc As Variant
        Arr = Array("1", "2", "3")
        On Error Resume Next
        lngLoc = Application.WorksheetFunction.Match("3", Arr(), 0)
        If Not IsEmpty(lngLoc) Then MsgBox lngLoc
    End Sub
    This will give you the position within the array that the matched value is located at.
    Last edited by firefytr; October 14th, 2005, 03:04.
    ___________
    Regards, Zack B. :|: Check out our Knowledge Base!

    Comment


    • #3
      Re: Check for presence of a value in an array

      OK, and can an array be declared as simply a named Range? Like, if I have values in the range B5 to B10, and I name that "myRange," and I want to use that as the array?

      Also, since I'm really just looking for a boolean out of this, not an actual location value, would I be able to use the MatchFound property instead for my purposes?
      Last edited by slickity; October 15th, 2005, 02:24.

      Comment


      • #4
        Re: Check for presence of a value in an array

        OK, after inspecting the MatchFound property, it doesn't seem I can use it for this.

        The code below is what I have so far. It gives me a Type Mistmatch error at the array initialization line, on the 3rd line of code. Any thoughts?
        Code:
         Sub isEmailNeeded(needsEmail)
        
            Dim adressExceptions As String
            adressExceptions = Array("Project Coordinator South")
            
            needsEmail = True
            If (ActiveCell.Value = "" And ActiveCell.Offset(0, 3).Value = "") Then
            needsEmail = False
            Else: If (Application.WorksheetFunction.Match(ActiveCell.Offset(0, 3).Value, adressExceptions, 0) = 0) Then needsEmail = False
            End If
            
        End Sub
        
        Sub testIsEmailNeeded()
        Dim testMe As Boolean
        testMe = True
        
        Range("A18").Activate
        
        Call isEmailNeeded(testMe)
        
        MsgBox needsEmail
        
        End Sub

        Comment


        • #5


          Re: Check for presence of a value in an array

          Don't declare your array as a String. This works for me...
          Code:
          Option Explicit
          
          Sub isEmailNeeded(needsEmail As Boolean)
              Dim rngOff As Range
              Dim adressExceptions()
              adressExceptions = Array("Project Coordinator South")
              Set rngOff = ActiveCell.Offset(, 3)
              needsEmail = True
              If (rngOff.Offset(, -3) = "" And rngOff = "") Then
                  needsEmail = False
              Else
                  If Application.WorksheetFunction.Match(rngOff.Value, adressExceptions, 0) <> 0 Then needsEmail = True
              End If
              MsgBox needsEmail
          End Sub
           
          Sub testIsEmailNeeded()
              Dim testMe As Boolean
              testMe = True
              Range("A18").Activate
              Call isEmailNeeded(testMe)
          End Sub
          ___________
          Regards, Zack B. :|: Check out our Knowledge Base!

          Comment

          Working...
          X