VBA InStr Question

[Ark68]

I have this line of code that is meant to determine where in a string (value at ws_ifm.cells(trow,2)), a substring (host) starts. Everything works except when the value in the cell equals the value of host.
eg - ws_ifm.cells(trow,2).value = "Cheese", and host = "Cheese"

The code returns a value of 0 which would suggest the sub string doesn't exist in the main string, which in reality, the substring is the main string.

Is there a way to change this code to capture this (I would think the formula would return a value of 1). Or should I just test if the values are equal first?

startPosition = InStr(1, ws_ifm.Cells(trow, 2), host, vbTextCompare)
     If startPosition = 0 Then
     '... execute code recognizing the value of host does not exist in the cell value

 

[Micron]

Put this before startPosition line and step through the code. Make sure it prints out two values that are exactly the same.
debug.print ws_ifm.Cells(trow, 2)
debug.print host
If the string and the substring are exactly the same, the function will return 1, not 0. If you're getting 0 then they are not the same. You can prove this in the immediate window: ?instr("Dog","Dog") and hit enter at the end of that line.

 

[Fluff]

That should return 1 if both values are exactly the same, which suggests that they aren't.

 

[JEC]

what if you write the instr like this?

InStr(ws_ifm.Cells(trow, 2), host)

 

[Ark68]

Thank you everyone for the diagnostic advice. It turns out that the values were not equal. The value in the cell had a leading space. I used a string length to compare the two and fopund one was one character longe than the other.
JEC, that worked now that I found the mismatch, not sure how it would have with the error. Probably resuult in zero as well.

 

[Micron]

Glad we could help & thanks for the recognition.

 

[Fluff]

Without specifying the final argument it will use the default which is case sensitive

 

[RoryA]

Unless you have

Option Compare Text

at the top of the module of course.