InSTR Compare erorr

[dakke]

I need a small adjustment to the following code.

(InStr(CStr(rangeArray(rIndex, 2)), testString) changed into
(InStr(CStr(rangeArray(rIndex, 2)), testString, 1) to get a case insensitve
textcompare. But it returns an error. What am I doing wrong?

The full code:

Function matchArray(ByVal testString As String, ByVal dataRange As Range,
Optional IndexNum As Long) As Variant
Dim outRRay() As String
Dim rangeArray As Variant
Dim xColl As New Collection
Dim xVal As Variant
Dim rIndex As Long

With dataRange.Parent
Set dataRange = Application.Intersect(dataRange, .UsedRange)
End With
With dataRange.Resize(, 3)
rangeArray = .Value
End With

For rIndex = 1 To UBound(rangeArray, 1)
If (InStr(CStr(rangeArray(rIndex, 2)), testString) > 0) Or
(InStr(CStr(rangeArray(rIndex, 3)), testString) > 0) Then
xVal = CStr(rangeArray(rIndex, 1))
On Error Resume Next
xColl.Add Item:=xVal, key:=xVal
On Error GoTo 0
End If
Next rIndex
ReDim outRRay(1 To Application.Max(Application.Caller.Cells.Count,
xColl.Count, IndexNum))

For rIndex = 1 To xColl.Count
outRRay(rIndex) = xColl(rIndex)
Next rIndex


If IndexNum < 1 Then
matchArray = outRRay
Else
matchArray = outRRay(IndexNum)
End If

End Function

 

[Joel]

I'm not getting an error with Excel 2003. I think it must have to do with
the data you are using. I tried forcing errors using different data patterns
but was not successful.

Try removing your data and working with a a few cell filled with data and
see if you can isolate where the problem is located.

 

[Rick Rothstein \(MVP - VB\)]

InStr is a strange function in that it has an optional first argument.
However, in order to make use of the compare argument, you **must** specify
the optional first argument. Try it this way...

InStr(1, CStr(rangeArray(rIndex, 2)), testString, 1)

By the way, it is more descriptive if you use the predefined constant for
the fourth argument...

InStr(1, CStr(rangeArray(rIndex, 2)), testString, vbTextCompare)

Rick

 

[dakke]

That was spot on Rick.

Indeed I needed to add the first option too. Thanks a lot.