How to find if an array contains a string

Question

Possible Duplicate    How to search for string in MS Access VBA array       I am currently working on an Excel macro  and I could not find a way to do like if array contains mystring   I wrote the following  and it gives me the message  Invaild Qualifier  and highlights the Mainfram right after If  Dim Mainfram 4  As String  Mainfram 0     apple   Mainfram 1     pear   Mainfram 2     orange   Mainfram 3     fruit       For Each cel In Selection         If Mainfram Contains cel Text  Then             Row cel Row  Style    Accent1          End If     Next cel   The selection is a column  Anyone help   Hi  JP I tried your suggestion  and it said Object required  And Highlightd the  If IsInArray cell Text  Mainfram  Then Heres my full code  Sub changeRowColor    Columns  B B   Select  Dim cel As Excel Range Dim Mainfram 4  As String  Mainfram 0     apple  Mainfram 1     pear  Mainfram 2     orange  Mainfram 3     Banana   For Each cel In Selection     If IsInArray cell Value  Mainfram  Then         Rows cel Row  Style    Accent1      End If Next cel  End Sub  Function IsInArray stringToBeFound As String  arr As Variant  As Boolean      IsInArray    UBound Filter arr  stringToBeFound    gt  -1   End Function   Nevermind  I found that stupid Error    Thank you anyways

User · Answer

I m afraid I don t think there s a shortcut to do this - if only someone would write a linq wrapper for VB6   You could write a function that does it by looping through the array and checking each entry - I don t think you ll get cleaner than that   There s an example article that provides some details here  http   www vb6 us tutorials searching-arrays-visual-basic-6

User · Answer

Another simple way using JOIN and INSTR  Sub Sample       Dim Mainfram 4  As String  strg As String     Dim cel As Range     Dim Delim As String      Delim            Mainfram 0     apple      Mainfram 1     pear      Mainfram 2     orange      Mainfram 3     fruit       strg   Join Mainfram  Delim      strg   Delim  amp  strg      For Each cel In Selection         If InStr 1  strg  Delim  amp  cel Value  amp  Delim  vbTextCompare  Then           Rows cel Row  Style    Accent1      Next cel End Sub

User · Answer

Use the Filter   method as shown here - https   docs microsoft com en-us office vba language reference user-interface-help filter-function

User · Answer

Using the code from my answer to a very similar question   Sub DoSomething   Dim Mainfram 4  As String Dim cell As Excel Range  Mainfram 0     apple  Mainfram 1     pear  Mainfram 2     orange  Mainfram 3     fruit   For Each cell In Selection   If IsInArray cell Value  MainFram  Then     Row cell Row  Style    Accent1    End If Next cell  End Sub  Function IsInArray stringToBeFound As String  arr As Variant  As Boolean   IsInArray    UBound Filter arr  stringToBeFound    gt  -1  End Function

[arrays] How to find if an array contains a string

Examples related to arrays

Examples related to vba

Examples related to substring