Results 1 to 2 of 2

check if value exists in a vba array

This is a discussion on check if value exists in a vba array within the Excel Questions forums, part of the Question Forums category; I want to check if a value exists in an array. And if so, take action. I do that with ...

  1. #1
    Board Regular
    Join Date
    Jul 2002
    Posts
    78

    Default check if value exists in a vba array

    I want to check if a value exists in an array. And if so, take action.
    I do that with the following code. But I don't think this is an elegant way of doing so :

    For Each k In projects()
    If k = "var" then
    'marker for value in array
    l = 1
    Else
    End If
    Next k

    If l = 1 Then
    **** do something *****
    End If

    anyone ?

  2. #2
    MrExcel MVP
    Moderator
    Andrew Poulsom's Avatar
    Join Date
    Jul 2002
    Posts
    70,330

    Default

    You can use the Match WorksheetFunction like this:

    Code:
    Sub Test()
        Const var As String = "var"
        Dim projects As Variant
        Dim x As Long
        projects = Array("x", "var", "y")
        On Error Resume Next
        x = WorksheetFunction.Match(var, projects, False)
        If Err = 0 Then
    '       **** do something ****
            MsgBox "Found"
        Else
    '       **** clear error ****
            Err.Clear
            MsgBox "Not Found"
        End If
        On Error GoTo 0
    End Sub

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


DMCA.com