Loading
Ozgrid Excel Help & Best Practices Forums

Excel Video Tutorials / Excel Dashboards Reports



Results 1 to 5 of 5

Thread: Check for presence of a value in an array

  1. #1
    Join Date
    8th 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 ...


    VB:
    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 03:04.

  3. #3
    Join Date
    8th 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 02:24.

    Excel Video Tutorials / Excel Dashboards Reports


  4. #4
    Join Date
    8th 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?
    VB:
    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...
    VB:
    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, 06:44
  2. Check If Value Exists In an Array
    By tkmanoj in forum EXCEL HELP
    Replies: 15
    Last Post: September 11th, 2007, 06:37
  3. Check If Array Is Set
    By Thor in forum EXCEL HELP
    Replies: 8
    Last Post: April 30th, 2007, 21:17
  4. Code to Check for Presence of Worksheet
    By smithch in forum EXCEL HELP
    Replies: 2
    Last Post: September 9th, 2006, 01:35
  5. [Solved] VBA: Check for presence of floppy disk
    By alexrowe in forum EXCEL HELP
    Replies: 5
    Last Post: December 16th, 2003, 23: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