How to find out if an array contains a string

Question

Possible duplicate:
How to search string in MS Access VBA array

I am currently working on an Excel macro and I could not find a way to do this if array.contains(mystring)

I wrote the following and it gives me the message "Invaild Qualifier" and selects 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 

Selection is a column

Can anybody help?

Hi JP I tried your suggestion and he said an object is required. And highlight 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)) > -1) End Function 

Nevermind, I found this stupid mistake ... Anyway, thanks

Answer 1

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)) > -1) End Function 

Answer 2

Another easy way: 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 & strg For Each cel In Selection If InStr(1, strg, Delim & cel.Value & Delim, vbTextCompare) Then _ Rows(cel.Row).Style = "Accent1" Next cel End Sub 

Answer 3

Use the Filter () method - http://msdn.microsoft.com/en-us/library/aa164525(v=office.10).aspx

Answer 4

I'm afraid I don’t think there is a shortcut for this - if only someone would write a linq wrapper for VB6!

You can write a function that does this by going through an array and checking each entry - I don't think you will be cleaner than that.

Here is an example article that provides some details here: http://www.vb6.us/tutorials/searching-arrays-visual-basic-6