Организация поиска в массиве с циклом

Sub TEST()
    Dim CompareRange As Variant, x As Variant, y As Variant, iEnd As Long, iStart As Long, q As Long, r As Variant
    Dim Compare As Variant, lRndNumber As Long, text As Variant, e As Variant
    ' q - Счётчик, r - ячейка в целом тексте, e - слово в табл.
    
 
    iStart = 1
    q = 0
    
    Set text = Range("A15:K18")
    Set CompareRange = Range("B5:B10") 'KeyWord
    Set Compare = Range("E5:E10") ' Таблица падежей
    
    For Each x In Compare
        For Each y In CompareRange
            If x = y Then iEnd = y.Offset(, -1).Value And e = x  'Если равны значения в массиве, вынимаем значение ячейки сбоку
            
            Next y
    Next x
            lRndNumber = Int((iEnd - iStart + 1) * Rnd) + Start 'Генерируем случайное число.
            
    Do
    Do While q < lRndNumber
    For Each e In Compare
        For Each r In text
    If x = r Then q = q + 1 And r.ClearContents
    Next r
    Next e
    
    Exit Do    ' Exit inner loop.
        Loop
        
Loop Until q = lRndNumber
 
ActiveCell.ClearContents
 
End Sub

If x = y Then iEnd = y.Offset(, -1).Value: e = x

Option Base 1
 
Private Function fun_СheckValueInArray(funArray(), _
                                ByVal funSerchInArray As Variant, _
                                        Optional fbNumbInArray As Boolean = False)
    ' Дана функція виконує перевірку одновимірного масиву:
           ' а) при fbNumbInArray = False _
                    тільки на наявність значення "funSerchInArray as Variant" _
                                в переданий масив "funArray". _
                  Поверне значення СheckValueInArray = True;
                  
           ' б) при fbNumbInArray = True _
                    визначає порядковий номер елемента _
                         funSerchInArray в масиві значень масиву funArray. _
                  Поверне значення порядкового номеру _
                    пошукуваного елементу в переданому масиві
    Dim fun_FiltrArr As Variant
    Dim funStr$, funDelimiter As String
    Dim i%, a%, funCounter%, elemNum As Integer
    Dim funNewStr As String
    
 
    If (Not Not funArray) = False Then
        MsgBox "Передача масиву ОБОВ'ЯЗКОВА!"  ' _
                                підтвердження, що було _
                                        передано пустий масив _
                                        (вираз "If Not Not mArr Then ..." _
                                         підтверджує, що масив заповнений).
        Exit Function
    End If
    
    funStr = ""
    funDelimiter = ";"
'-------------------------------------------------------------------------------------------
    ' Виконуємо перевірку на наявність значення в масиві:
    If fbNumbInArray = False Then
        fun_СheckValueInArray = False ' при наявності пошукованого _
                    значення, функція поверне СheckValueInArray = True
        fun_FiltrArr = Filter(funArray, _
                    funSerchInArray, True, vbBinaryCompare) ' _
                        фільтруємо масив funArray для зменшення _
                        кількості значень, серед який проводимо пошук.
                funStr = Join(fun_FiltrArr, funDelimiter) ' в строкову _
                        змінну, через крапку з комою, записуємо _
                            всі відфільтровані значення.
                    If InStr(1, UCase(funStr), UCase(funSerchInArray), _
                                                    vbBinaryCompare) <> 0 Then
                        fun_СheckValueInArray = True ' пошукуване значення _
                                    знайдено, функція повертає "True".
                        Erase fun_FiltrArr
                        Exit Function
                    End If
    End If
'==================================================
'-------------------------------------------------------------------------------------------
    ' Визначаємо порядковий номер _
                пошукуваного елемента в переданому масиві _
                            для цього при виклику потрібно було _
                                        вказати в параметрах "True":
    If fbNumbInArray = True Then
        funStr = Join(funArray, funDelimiter) ' в строкову _
                        змінну, через крапку з комою, записуємо _
                            всі відфільтровані значення.
            If InStr(1, UCase(funStr), UCase(funSerchInArray), _
                                                            vbBinaryCompare) <> 0 Then
                elemNum = InStr(1, UCase(funStr), UCase(funSerchInArray), _
                                                                    vbBinaryCompare) ' визначаємо _
                                        порядковий номер пошукуваного елементу.
                For i = 1 To Len(Mid(Trim(funStr), 1, elemNum))  '
                    If Trim(Mid(funStr, i, 1)) = funDelimiter Then _
                                                            funCounter = funCounter + 1
                Next i
                    Else
                        MsgBox "п.ф. В наданому масиві значення," & _
                                                        " яке Вас цікавить, відсутнє!"
            End If
            If elemNum <> 0 Then
                fun_СheckValueInArray = funCounter + 1 ' визначили _
                            порядковий номер переданого масиву, під _
                                        яким зберігається пошукуване значення.
            End If
    End If
'==================================================
End Function
 
Private Sub Test_fun_СheckValueInArray()
 
    Dim mVal As Variant
    Dim mArr()
    Dim OrderNumber As Integer
 
    mVal = 101
 
    mArr = Array(1, 11, 14, 15, 17, 21, 23, 46, _
                                    55, 79, 100, 101, 103, 111, 120, _
                                            167, 199, 201, 205, 309, 409, 591, 888, 902)
    mVal = 1001
'-----------------------------------------------------------------------------------------
'' Виконуємо переврку на наявність значення mVal _
                                                                            в масиві mArr:
'    If fun_СheckValueInArray(mArr, mVal) = True Then
'        MsgBox "s.t. Таке значення в масиві існує!"
'            Else: MsgBox "s.t. Значення   " & mVal & "   в масиві відсутнє!"
'    End If
'=================================================
'------------------------------------------------------------------------------------------
    ''визначаємо порядковий номер значення mVal _
                                                                            в масиві mArr:
    OrderNumber = fun_СheckValueInArray(mArr, mVal, True)
        If OrderNumber = 0 Then
            MsgBox "s.t. Значення   " & mVal & "   в масиві відсутнє!"
                Else
                   MsgBox "s.t. Індекс значення   " & mVal & _
                                    "   в масиві   -   " & OrderNumber
        End If
'    Stop
'=================================================
   
End Sub