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
Bookmarks