Loading
Ozgrid Excel Help & Best Practices Forums

Excel Training / Excel Dashboards Reports



Results 1 to 5 of 5

Thread: Check for presence of a value in an array

  1. #1
    Join Date
    9th September 2005
    Posts
    62

    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.

    Excel Video Tutorials / Excel Dashboards Reports


  2. #2
    Join Date
    30th March 2004
    Location
    Oregon, US
    Posts
    166

    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 at 04:04.

  3. #3
    Join Date
    9th September 2005
    Posts
    62

    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 at 03:24.

    Excel Video Tutorials / Excel Dashboards Reports


  4. #4
    Join Date
    9th September 2005
    Posts
    62

    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

    Excel Video Tutorials / Excel Dashboards Reports


  5. #5
    Join Date
    30th March 2004
    Location
    Oregon, US
    Posts
    166

    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

Thread Information

Users Browsing this Thread

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

Possible Answers

  1. Check/Compare Array Elements
    By nb- in forum EXCEL HELP
    Replies: 2
    Last Post: May 12th, 2008, 07:44
  2. Check If Value Exists In an Array
    By tkmanoj in forum EXCEL HELP
    Replies: 15
    Last Post: September 11th, 2007, 07:37
  3. Check If Array Is Set
    By Thor in forum EXCEL HELP
    Replies: 8
    Last Post: April 30th, 2007, 22:17
  4. Code to Check for Presence of Worksheet
    By smithch in forum EXCEL HELP
    Replies: 2
    Last Post: September 9th, 2006, 02:35
  5. [Solved] VBA: Check for presence of floppy disk
    By alexrowe in forum EXCEL HELP
    Replies: 5
    Last Post: December 17th, 2003, 00:27

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