Menu
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
730
Check if a String is contained in an Array using indexOf # We used the Array. indexOf method to check if the string two is contained in the array. If the string is not contained in the array, the indexOf method returns -1 , otherwise it returns the index of the first occurrence of the string in the array.
Using the indexOf() method In this method, what we do is that we compare the index of all the items of an array with the index of the first time that number occurs. If they don't match, that implies that the element is a duplicate. All such elements are returned in a separate array using the filter() method.
Using Set A simple and elegant solution is to construct a set from the array which retains only distinct elements. Then simply compare the set's size against the array's length. If both are not the same, then we can say that the array contains duplicates. This works in linear time and space.
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 If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
