Excel 2010 InStr function problem

[VBAPrincess]

I was helping someone add the final touches to a long process and ran into a strange problem with InStr. The long process included a macro which is filtering data raw data by client and copying the data to a new worksheet. On the new worksheet, a pivot table of the pasted raw data is created. To the left of the pivot table, there is a column which needs to be populated with a value from another smaller dataset just above the pivot table. The value we are looking for is a short string (maybe 10 char) and it needs to be found in a longer string (maybe 30 char) from the Row Value in the pivot table. We decided to create a function which he could enter and autofill next to the pivot table. (currently he's looking up the item numbers by hand on 15+ plus worksheets)

Example:
Item#A | DescA
1 | XYZ 7 2013
etc...

Item#B | DescB
? | Northwind XYZ 7 2013 Qtr 1 ABC

Function GetItemNumber(rLookIn As Range, strCheck As String)
Dim cell As Variant, strFound As String

For Each cell In rLookIn
    If InStr(strCheck, cell.Value) > 0 Then
        strFound = cell.Offset(0, -1).Value
        Exit For
    End If
Next
GetItemNumber = strFound
End Function

When I ran the code, the Instr function would return 0 as if it did not find the string so none of the item numbers were captured. However, if I ran in debug mode and tested the Instr function in the immediate window, the function would return a value (like 18 for example). It was very strange! I would check the values to be compared in the immediate window and then use the values returned in a call to InStr.

Any idea what could be wrong? I tested the function in another workbook with other data and it works as expected. I realize the question might be, why didn't you use VLOOKUP but the strings will never be an exact match and I wasn't sure an approximate match would be reliable. If someone has a suggestion for using Excel functions to accomplish the same, let me know. My first reaction when asked, how would I do this was to say use InStr and then Offset. Everyone has their own way to approach a problem. I was frustrated that InStr wasn't working like it was in the immediate window.

Thanks in advance for assistance!

Diana
VBA Princess
-- I'm hoping to grow up to be a Goddess!

 

[PHV]

How did you test the Instr function in the immediate window ?
Anyway, I'd try this in the function:
If InStr(1, strCheck, cell.Value, 1) > 0 Then

Another way:
If LCase(strCheck) Like "*" & LCase(cell.Value) & "*" Then

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[vbajock]

Try:

http://www.techonthenet.com/excel/formulas/instrrev.php

 

[vbajock]

.. .. see if you get the same results working it backwards, if so the error is somewhere else.

What version of Excel are you using? I noticed while googling this that there were bugs in Instr, but in way-back Excel 97 & 2000

 

[VBAPrincess]

PHV: I tested in the immediate window like so -- ?strCheck and ?cell.value, then I tried ?Instr(actual values returned in window for strCheck and cell.value) Thanks for the suggestion and I'll have my colleague try your suggestion.

vbajock: We'll have to try it in reverse and see what happens. This is Excel 2010 so hopefully there aren't any bugs that could be impacting this.

many thanks!

Diana
VBA Princess
-- I'm hoping to grow up to be a Goddess!