find string pos from strings in an array

I am trying to find the string position of a string, and the string I am looking FOR is from an array of terms in another worksheet. I have it looking like this, but am getting a "type mismatch error" on this line...

termPos = InStr(Cell.Value, termsArray)

Here is my entire code. Any help will be appreciated.

Sub find_term_position()

Dim i As Long
Dim termsArray() 

Dim termsCount As Integer
Dim prodsCount As Integer
Dim termPos As Integer

Dim Cell As Range

Dim wsProds As Worksheet
Dim wsTerms As Worksheet

Set wsProds = Worksheets("products")
Set wsTerms = Worksheets("terms")

'number of terms
termsCount = wsTerms.Range("A1").End(xlDown).Row 

ReDim Preserve termsArray(wsTerms.Range("A1:A" & termsCount))

For i = LBound(termsArray) To UBound(termsArray)
    termsArray(i) = wsTerms.Range("A" & i + 1)
Next i

'number of products
prodCount = wsProds.Range("A1").End(xlDown).Row 

For Each Cell In wsProds.Range("A1:A" & prodCount)
    
       termPos = InStr(Cell.Value, termsArray)

        If termPos > 0 Then
        'string found
        Cell.Offset(0, 1).Value = "Term found at: " & termPos
    Else
        'string not found
        Cell.Offset(0, 1).Value = "Term not found"
   End If

Next Cell

End Sub

Thanks, but match function won't work for my needs. I need to have the position so that I can use the mid function to make modifications.

So to use inStr, I will need to loop through the array. Right?

That's what the match function does. Read shg's post again.

it looks like MATCH does just the opposite. It looks for a substring within an array. My array contains the substrings, and I need to find substring position within the string. Is there a way to do this?

I don't know where you're looking. Why don't you attach a sample of what you're doing?

Hello friscokid,

This version of your macro takes advantage of the Range Objects properties and methods to eliminate the need for nested looping and a dynamic array. This code can be modified to find multiple occurances of the substring quite easily, if you need that feature later.

Sub find_term_position()

  Dim ProdsRng As Range
  Dim termPos As Integer
  Dim TermsRng As Range
  Dim wsProds As Worksheet
  Dim wsTerms As Worksheet

    Set wsProds = Worksheets("products")
    Set wsTerms = Worksheets("terms")

      Set ProdsRng = wsProds.Cells(1, "A").CurrentRegion.Resize(Columnsize:=1)
      Set TermsRng = wsTerms.Cells(1, "A").CurrentRegion.Resize(Columnsize:=1)
      
        For Each Prod In ProdsRng
          Set Term = Terms.Find(What:=Prod, _
                                After:=Terms.Cells(1, 1), _
                                LookIn:=xlValues, _
                                LookAt:=xlWhole, _
                                SearchBy:=xlRows, _
                                SearchDirection:=xlNext, _
                                MatchCase:=False)
          
          If Not Term Is Nothing Then
            termPos = InStr(1, Prod, Term.Text)
              If termPos > 0 Then
                Prod.Offset(0, 1) = "Term found at: " & termPos
              Else
                Prod.Offset(0, 1) = "Term not Found"
              End If
          End If
        Next Prod

End Sub

Sincerely,
Leith Ross