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)) > -1)
End Function
Nevermind, I found that stupid Error... Thank you anyways
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
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 & 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
Use the Filter() method as shown here - https://docs.microsoft.com/en-us/office/vba/language/reference/user-interface-help/filter-function
Source: Stackoverflow.com