Does not cosistantly find a text string using "InStr" function

**Bobbbo** · 03-17-2016, 04:34 PM

This has my head spinning as it works in some cases and not in others. I am using the "instr" function to a word I am looking for. When found the result is greater than "0". Here are two examples of what is and is not working. "Taxes - Property" is in one cell and it finds it and "Payroll Taxes" is in another and does not find it. The same for the second example.

Example 1

If InStr(1, " " & UCase(ActiveCell.Value) & " ", UCase(" Taxes ")) > 0 Then

"Taxes - Property" (without quotes) (finds)
"Payroll Taxes" (without quotes) (does not find)

Example 2

If InStr(1, " " & UCase(ActiveCell.Value) & " ", UCase(" Depreciation ")) > 0 Then

"Depreciation Expense" (without quotes) (finds)
"Depreciation Expense (Depreciation on equipment, buildings and improvements)" (without quotes)(does not find)

Is there another parameter I have set incorrectly?

**MrShorty** · 03-17-2016, 04:43 PM

Your third argument is " Taxes " (note the space character at start and end of string).
The string "Payroll Taxes" does not contain the text string " Taxes " with a space character before and after the word Taxes. It contains the string " Taxes" (space before but not after), but this is not what you have asked it to look for.
The InStr function is working correctly. You need to decide exactly what purpose those extra space characters serve and what to do with them.

**Bobbbo** · 03-17-2016, 04:47 PM

The reason for the space, which has been work very well, is to avoid pulling the word out of the middle of another word. If you look ate the second example the start the exact same, but it is found in one but not the other.

**MrShorty** · 03-17-2016, 04:58 PM

I cannot replicate the problem. It finds " Depreciation " just fine in both cases.

Possible that there are "non-breaking" spaces or other non-printing characters in the search string?

It might be helpful to upload a sample spreadsheet with the actual text strings being tested.

**Bobbbo** · 03-17-2016, 05:12 PM

I will do that. See attached

**MrShorty** · 03-17-2016, 05:17 PM

Still cannot replicate the problem. In both cases, it finds " Taxes " just fine.

**Bobbbo** · 03-17-2016, 05:21 PM

Strange, my computer does not. Could there be something broken in my excel?

**MrShorty** · 03-17-2016, 05:28 PM

I expect it is "possible", but there is no way we can know on this side of the internet. I did get a warning message about this file's format not matching its extension, though the file opened just fine. Does it work correctly if you start from a clean, new file?

All you have provided are code snippets and one example that worked. Are there more examples? Is there more to the code?

This worked correctly for me.

**Bobbbo** · 03-17-2016, 05:41 PM

I have no idea what is going on. I get the same message when opening up the file. I reset my computer and even save the file as another name and the message went away.

**AB33** · 03-17-2016, 06:00 PM

It does not matter if you have a space or not, nor does if it is case sensitive as long as you have option compare 1.

If InStr(1, ActiveCell.Value, "Taxes", 1) > 0 Then Debug.Print "hello"

**Bobbbo** · 03-20-2016, 01:59 PM

Hi AB33. Are you saying your code:

If InStr(1, ActiveCell.Value, "Taxes", 1) > 0 Then Debug.Print "hello"

will work better than mine?

If InStr(1, " " & UCase(ActiveCell.Value) & " ", UCase(" Taxes ")) > 0 Then Debug.Print "hello"

**Bobbbo** · 03-17-2016, 08:52 PM

It turns out to be a problem on my part. Thanks all for the help. AS33, are you certain that you do not need a space. If the sting I am looking for is "def" and and the cell has "ABCDEFHIJ" I do not want it to say found even though "DEF" is within the string.

**jindon** · 03-17-2016, 11:13 PM

See if this works correctly on you side.
Result should be True/True/False

Sub test()
    Dim e
    For Each e In Array("Payroll Taxes", "Taxes - Property", "TaxesABC")
        MsgBox myFind(e), , e
    Next
End Sub

Function myFind(ByVal txt As String) As Boolean
    With CreateObject("VBScript.RegExp")
        .IgnoreCase = True
        .Pattern = "\b(taxes|description)\b"
        myFind = .test(txt)
    End With
End Function

**AB33** · 03-21-2016, 05:17 PM

Yes, you do not need to complicate by adding upper cases as long as you have added option compare insensitive (that is 1)

**Bobbbo** · 03-21-2016, 08:37 PM

AB33, Thanks!

Does not cosistantly find a text string using "InStr" function

LinkBack

Thread Tools

Rate This Thread

Display

Hybrid View

Does not cosistantly find a text string using "InStr" function

Re: Does not cosistantly find a text string using "InStr" function

Re: Does not cosistantly find a text string using "InStr" function

Re: Does not cosistantly find a text string using "InStr" function

Re: Does not cosistantly find a text string using "InStr" function

Re: Does not cosistantly find a text string using "InStr" function

Re: Does not cosistantly find a text string using "InStr" function

Re: Does not cosistantly find a text string using "InStr" function

Re: Does not cosistantly find a text string using "InStr" function

Re: Does not cosistantly find a text string using "InStr" function

Re: Does not cosistantly find a text string using "InStr" function

Re: Does not cosistantly find a text string using "InStr" function

Re: Does not cosistantly find a text string using "InStr" function

Re: Does not cosistantly find a text string using "InStr" function

Re: Does not cosistantly find a text string using "InStr" function

Thread Information

Users Browsing this Thread

Similar Threads

How to use InStr function to search for a text that starts with "CTF" and ends with "."?

create text string: "NOT "[b2value]", NOT "[b3value]", ..., NOT "[b600value]"

[SOLVED] Combo list selected by "contains" text string entered rather than "begins with"

[SOLVED] Find text string after the ", " then move its down to the last row

Instr() function giving error: "Type mismatch"

[SOLVED] Search numbers in a text string, find largest "value" return a value

[SOLVED] Combining a text string with a concatenate function in the "value if true" field of an IF

Bookmarks

Bookmarks

Posting Permissions