Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
InSTR Compare erorr
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
InSTR Compare erorr
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. "dakke" wrote: 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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
InSTR Compare erorr
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" wrote in message ... 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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
InSTR Compare erorr
That was spot on Rick.
Indeed I needed to add the first option too. Thanks a lot. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Solver internal erorr message | Excel Discussion (Misc queries) | |||
How to fix this erorr (Selected Collating sequence not support by theoperating system) | Excel Programming | |||
i have erorr on my email on yahoo why? | Excel Programming | |||
Why is this an erorr? | Excel Programming | |||
Erorr when 'edit query' | Excel Discussion (Misc queries) |