Getting a type mismatch using Match or any Search Function-CodePudding

I have been trying to get this to work for two days now. I am trying to find a specific string of words in my worksheet, where I have similar strings of words (ex: Yellow-Banana-F and Yellow-Banana-WF). I have tried changing my code many different times to different ways (like String vs Long for Dim ILS1/2), and can only successfully find the first row containing one of these values, even though I am trying to find all applicable matches and sort them into my userform (ex: if Yellow-Banana-F is a row above ̀ Yellow-Banana-WF, then my code will only find and display Yellow-Banana-F` in the correct spot, but the other one will not show at all).

The current code is throwing an error

"Run time error 13: type mismatch."

I have also tried to use loops but I cannot seem to get that to work either. I am self taught with VBA and do not have extensive knowledge so perhaps I am missing something very simple here, but either way I am happy to hear any suggestions!

Current code:

Private Sub CHK1_change()
    Dim sh6 as Worksheet
    Set sh6 = ThisWorkbook.Sheets("OOS")
    Dim ILS1 As String
    Dim ILS2 As String

    ILSA1 = Me.txtILS.value & "-WF"
    ILSA2 = Me.txtILS.value & "-F"

    ILS1 = Application.Match(VBA.CStr(ILSA1), sh6.Range("B:B"), 0)
    ILS2 = Application.Match(VBA.CStr(ILSA2), sh6.Range("B:B"), 0)

    If sh6.range("F" & ILS1). value <> "" then
        me.txtILSA1.value = "WF"

    If sh6.range("F" & ILS2).value <> "" then
        me.txtILSA2.value = "F"

End Sub

CodePudding user response：

Using `Application.Match`

It is a good idea to write the result to a variant variable (sIndex).
The result (sIndex) wiil either be a whole number referring to the index (position) of the first found value, or an error value if no match.
You can test the result (sIndex) in the following two ways:

If IsNumeric(sIndex) Then ' used in the code
If IsError(sIndex) Then

A Quick Fix

Option Explicit

Private Sub CHK1_change()
    
    Dim sh6 As Worksheet: Set sh6 = ThisWorkbook.Worksheets("OOS")
    
    Dim Suffixes As Variant: Suffixes = Array("WF", "F")
    Dim TextBoxes As Variant: TextBoxes = Array(Me.txtILSA1, Me.txtILSA2)
    
    Dim ILSA As String
    Dim sIndex As Variant
    
    Dim n As Long
    For n = LBound(Suffixes) To UBound(Suffixes)
        ILSA = Me.txtILS.Value & "-" & Suffixes(n)
        sIndex = Application.Match(ILSA, sh6.Range("B:B"), 0)
        If IsNumeric(sIndex) Then ' is number
            If sh6.Range("F" & sIndex).Value <> "" Then ' cell not blank
                TextBoxes(n).Value = Suffixes(n)
            'Else ' cell (in column 'F') is blank
            End If
        'Else ' is error value
        End If
    Next n
    
End Sub

Using Application.Match

Using `Application.Match`