Match from Array

[Deepk]

hi all,

I am stuck in a macro. find below my requirement:

I have a VBA array such as CodeClean = array (AB, BC, ZA, CT, YR, UT..........)
I want to enter few data in an inputbox. The data are like BC, CT
If the any individual entry in the inputbox does not match with value provided in array then it shows a msgbox "entries not found". Example of inputbox entries TT, AB.
If all the entries match with array value then msgbox "entries found" will appear. Example of inputbox entries BC, CT.

I dont want case1 case2 types code. if possible use "If" command.

I hope my requirement is clear. Thank you

 

[pike]

Hello,
maybe,,

Option Explicit
Sub test()
    Dim CodeClean
    CodeClean = Application.Match(InputBox("Input Value", "InputBox"), Application.Transpose(VBA.Array("AB", "BC", "ZA", "CT", "YR", "UT")), 0)
    If IsError(CodeClean) Then
        MsgBox "entries not found"
    Else
        MsgBox "entries found"
    End If
End Sub

 

[Deepk]

Hello,
maybe,,

Option Explicit
Sub test()
    Dim CodeClean
    CodeClean = Application.Match(InputBox("Input Value", "InputBox"), Application.Transpose(VBA.Array("AB", "BC", "ZA", "CT", "YR", "UT")), 0)
    If IsError(CodeClean) Then
        MsgBox "entries not found"
    Else
        MsgBox "entries found"
    End If
End Sub

Hi Pike,

Thank you for your code. However, this code is not fit for my requirements.

Please not that my inputbox has more than one entries (comma separated) such as AB, UC, RT
The code should match the all individual entries. In case even a single entry is NOT matched with array list then MsgBox "entries not found" should appear.
In case all individual entries found then MsgBox "entries found" should appear.

I hope this is clear.

 

[pike]

something like,,

Option Explicit
Sub test()
    Dim CodeClean, eItem, strInput As String, strMsg As String
    strInput = InputBox("Input Value", "InputBox")
    For Each eItem In Split(strInput, ",")
        CodeClean = Application.Match(eItem, Application.Transpose(VBA.Array("AB", "BC", "ZA", "CT", "YR", "UT")), 0)
        If IsError(CodeClean) Then
            strMsg = strMsg & Chr(13) & eItem & Space(1) & "entry not found"
        Else
            strMsg = strMsg & Chr(13) & eItem & Space(1) & "entry found"
        End If
    Next
    MsgBox strMsg
End Sub