Excel InStr not Matching

Question

I believe that I'm having trouble with InStr here.

I have a for loop, looping through a bunch of values, and exiting correctly when a cell contains "--".

However, if the cell contains text that cound be found in Range("A2"), then I want it to spit out it's value to Range("A5").

Private Sub CommandButton21_Click()
    Dim cell As Range
    For Each cell In Sheets(1).Range("$B:$B")
        Dim i As Long
        i = cell.Row + i
        If InStr(1, cell.Text, Range("A2").Text, vbTextCompare) Then Range("A5").Value = cell.Text
        If cell.Text = "--" Then Exit For
    Next cell
End Sub

No idea why this sort of compare is failing.

As I understand your narrative, you've flipped the *Search For* and *Within Text* parameters. VBA's `InStr`'s parameters are the opposite order of the worksheet's `Find` or `Search` function. — , Feb 26 '15 at 00:16
i would also like to confirm that you know what you are asking for. The `.Text` property of a range (aka *cell*) is the displayed text and subject to cell formatting. A cell with `=TODAY()` formatted as *dd-mmm-yyyy* will gave a `.Text` of 25-Feb-2015 a `.Value` of 02/25/2015 and a `.Value2` of 42060. — , Feb 26 '15 at 00:22
pardon if i am wrong but I think the `dim i` should be outside the `for each` loop — brietsparks, Feb 26 '15 at 00:25
Put a debug breakpoint on the line with `InStr` and make sure you get a known-true condition. Then figure out what the value of `cell.Text` is. — theMayer, Feb 26 '15 at 01:36
I've ended up using ```like``` instead. ```If cell.Text Like "*" & Range("A2") & "*" Then Range("A5").Value = cell.Text``` matches like a charm. And yes, I did need to instantiate ```i``` outside the ```for``` loop. — s4b3r6, Feb 26 '15 at 03:42

score 0 · Answer 1 · edited May 23 '17 at 12:20

0

InStr returns a numeric value equal to the starting position of the search string within the searched field so you would have to compare it against a number:

If InStr(1, cell.Text, Range("A2").Text, vbTextCompare) > 0 Then Range("A5").Value = cell.Text

On a side note, if you don't need it then it's faster to process a .Value2 then a .Text - see this question.

edited May 23 '17 at 12:20

Community

1
1

answered Feb 26 '15 at 00:36

D_Zab

710
1
5
14

1

Should be `InStr(1, cell.Text, Range("A2").Text, vbTextCompare) > 0` otherwise it only picks up things from 2nd character. – PatricK Feb 26 '15 at 04:09

Excel InStr not Matching

1 Answers1