instr issue with matching string in another string

[ExcelEveryday1]

Hi guys,

Instr doesnt seem to be matching properly for me, and I dont know what Im doing wrong. I get a match if my criteria value is the same as my search location value, but if the string is in the other string without matching it exactly, my code doesnt pick up on it.

----------------------------------------
dim cell as range
dim range1 as range
dim ws as worksheet

For Each cell In range1


If InStr(ws.Cells(10036, 4).Value, cell.Value) <> 0 Then
ws.Cells(10036, 2).Value = "freedom"
Else
End If
Next cell
-------------------------------------------

As an example of what I'm looking for:

(ws.Cells(10036, 4) = 2ABC_123
cell.value = "2ABC"

I also tried adding in an & "*" to my cell.value, but that didnt seem to help


Thanks

 

[Eric W]

Try reversing the parameters:

If InStr(cell.value, ws.Cells(10036, 4).Value) <> 0 Then

 

[ExcelEveryday1]

Try reversing the parameters:

If InStr(cell.value, ws.Cells(10036, 4).Value) <> 0 Then

I tried this, and it worked for some values, but not others

Precisely what Im trying to match is 2ABC from another worksheet with

2ABC_CBDC. My 7 others tests with others cells have come out positive, however this one doesnt work.

I tried replacing .value with .text, and adding in wildcards everywhere. I still cant get a match. Any other ideas!?

 

[Eric W]

Are you looking for the 2ABC within the 2ABC_CBDC, (which would be: Instr("2ABC_CBDC","2ABC") or InStr(ws.Cells(10036, 4).Value, cell.Value) which you had), or vice versa ( InStr(cell.value, ws.Cells(10036, 4).Value) which I proposed)? Or are you just checking to see if they are a "close" match? If that's the case then you could use an OR:

If InStr(ws.Cells(10036, 4).Value, cell.Value) > 1 Or InStr(cell.Value, ws.Cells(10036, 4).Value) > 1 Then

There are other ways of determining "closeness" too.

Does the cell value actually have the quotes on it? If so, we'd need to strip them off to find a match.

 

[ExcelEveryday1]

Are you looking for the 2ABC within the 2ABC_CBDC, (which would be: Instr("2ABC_CBDC","2ABC") or InStr(ws.Cells(10036, 4).Value, cell.Value) which you had), or vice versa ( InStr(cell.value, ws.Cells(10036, 4).Value) which I proposed)? Or are you just checking to see if they are a "close" match? If that's the case then you could use an OR:

If InStr(ws.Cells(10036, 4).Value, cell.Value) > 1 Or InStr(cell.Value, ws.Cells(10036, 4).Value) > 1 Then

There are other ways of determining "closeness" too.

Does the cell value actually have the quotes on it? If so, we'd need to strip them off to find a match.

I need to make sure that cell ws.Cells(10036, 4).Value contains the value of cell.value. Eventually I will replace ws.cells(10036,4) with more variables so I can loop the search of cell.value for another list of cells in ws.

I just tried instr("2ABC", ws.Cells(10036, 4), and it still didn't give me a match. I then tried instr( "2ABC_CBDC",ws.Cells(10036, 4)) and it matched. This means, it is not giving me a partial match, even though I can see that 2ABC is in 2ABC_CBDC. Am I wildcarding my instr "2ABC" search? How would you wildcard this?

I am absolutely lost as to why this is occurring for this cell combination and not others ( some of which share identical letter patterns with different letters). Also, there are no quotes in my cells.

Thanks again for your help!

 

[Eric W]

I'm a bit stumped too from your description. Unless there's some unprintable characters in one or the other cell. You can test for those with the Len function.

Instr does not use wildcards, and trying to add them will cause problems. If you want to try wildcards, you can use the Like operator:

If ws.Cells(10036, 4).Value Like "*" & cell.Value & "*" Then

That should be true if cell.Value is within ws.Cells(10036, 4)

 

[daverunt]

To find a partial match shouldn't you be using?

If InStr("SourceString", "WantedString") > 0 Then

 

[ExcelEveryday1]

Thank you so much for your help. I found my (dumb) mistake. Cell wasnt defined to a worksheet, so if the activesheet wasnt the sheet with cell values, it wouldnt match :/

so I
dim ws3 as worksheet
set ws3 as thisworkbook.sheets(3)

but now if I try to add If InStr(ws.Cells(10036, 4).Value, ws3.cell.Value) <> 0 Then

I get a "Method or data member not found"

what am i doing wrong?

 

[mikerickson]

Might you be running into case sensitivity issues?

InStr(1, ws.Cells(10036, 4).Value, ws3.cell.Value, vbTextCompare)

 

[Eric W]

I should have thought of that! As far as the new error, you don't need to add the ws3 to the cell.value. Since you have

For Each cell In range1

as the loop control, "cell" is already defined as having a sheet, that sheet being wherever range1 is defined. So just leave the If line as:

If InStr(ws.Cells(10036, 4).Value, cell.Value) <> 0 Then