Match from Array

Deepk

Board Regular
Joined
Mar 21, 2018
Messages
100
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
 

Some videos you may like

Excel Facts

Ambidextrous Undo
Undo last command with Ctrl+Z or Alt+Backspace. If you use the Undo icon in the QAT, open the drop-down arrow to undo up to 100 steps.

pike

Board Regular
Joined
Sep 16, 2007
Messages
231
Hello,
maybe,,
Code:
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

Board Regular
Joined
Mar 21, 2018
Messages
100
Hello,
maybe,,
Code:
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.
 
Last edited:

pike

Board Regular
Joined
Sep 16, 2007
Messages
231
something like,,
Code:
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
 

Watch MrExcel Video

Forum statistics

Threads
1,122,162
Messages
5,594,615
Members
413,917
Latest member
devansh02

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top