Instr matches more then I want.

[n35]

Using the following code

Sub LoopCells()
Sheets("RawData").Select
Sheets("RawData").Activate
LRApproved = Cells(Rows.Count, "H").End(xlUp).Row
LRsource = Cells(Rows.Count, "G").End(xlUp).Row
For Each approvedcell In Worksheets("RawData").Range("H2:H" & LRApproved).Cells 'Approved stores entered by users
    For Each sourcecell In Worksheets("RawData").Range("G2:G" & LRsource).Cells 'items fround from bank statement export
       If InStr(UCase(sourcecell.Value), UCase(approvedcell.Value)) <> 0 Then
                   sourcecell.Offset(0, 2).Value = approvedcell.Value
       End If
    Next sourcecell
Next approvedcell
End Sub

I create a list of approved names based on a list of existing approved names, and a source list. So the source list is scanned and if an item matches something which is in the approved names list, it is added to a different column.

The problem is that based on my source data, and my approved names - i sometimes get the wrong result, I know why - I just don't know how to fix it.

Sample data - Source List
Super Brugsen Had
SuperBest HJo
SU


Approved Names list
Super Brugsen
SuperBest
SU

Running the loopcells on that list, will produce

SU
SuperBest
SU

istead of
Super Brugsen
SuperBest
SU

Any ideas on how i correct this behavior?

 

[vaskov17]

Sub LoopCells()
Sheets("RawData").Select
Sheets("RawData").Activate
LRApproved = Cells(Rows.Count, "H").End(xlUp).Row
LRsource = Cells(Rows.Count, "G").End(xlUp).Row
For Each approvedcell In Worksheets("RawData").Range("H2:H" & LRApproved).Cells 'Approved stores entered by users
    For Each sourcecell In Worksheets("RawData").Range("G2:G" & LRsource).Cells 'items fround from bank statement export
       If InStr(UCase(sourcecell.Value), UCase(approvedcell.Value)) <> 0 Then
                   sourcecell.Offset(0, 2).Value = approvedcell.Value
                   [COLOR="Red"]Exit For[/COLOR]
       End If
    Next sourcecell
Next approvedcell
End Sub

 

[n35]

My apologies for not being clear enough, i am interested in obtaining three results.


The input

Super Brugsen Had
SuperBest HJo
SU

Should via the for loop be outputted to a different column with

Super Brugsen
SuperBest
SU

Using the Exit For, will make it stop after 2 runs, the example input list contains 3 items, the actual list contains well over 200 but I am getting false positives, due to SU being a part of Super as well as a few others.

I am guessing i need to do something similar to make it search on whole words or something.

The problem is that Super Brugsen, triggers the same response as SU, I am guessing its

 

[vaskov17]

did you try the code I posted? also it's better if your outside loop is the sourcecell one and the inside one is approvedcell

 

[n35]

Vaskov, thanks for your reply, but yes I did, and it stops after the second run. Leaving an output list of
SU
SuperBest

Which means I am missing one, plus the out of the two it outputs, one is wrong. Instead of SU it should be Super Brugsen.

Switching the other of the loops still provides an output list with
SU mentioned twice, instead of the correct output of

Super Brugsen
SuperBest
SU

 

[Rick Rothstein]

The way I do exact searches using InStr is to surround the text being searched and the text I am searching for with the delimiter (space characters in the case of words). For example, to find the location of the word "the" in this sentence "My bother came to the party" and avoid the "the" inside the word "brother"...

FindMe = "the"
SearchMe = "My brother came to the party"
Location = InStr(" " & SearchMe & " ", " " & FindMe & " ")

InStr still returns 0 if the text cannot be found as a "stand-alone word".

 

[n35]

Thats a very good suggestion, I did not consider trying that.

Having done that, it provides two of the three correct answers, it skips the first answer.
Super Brugsen, I am not too sure why, I even tried various permutations of adding a space to the arguments.

   If InStr(" " & UCase(sourcecell.Value) & " ", " " & UCase(approvedcell.Value) & " ") <> 0 Then

But to no avail, either it skips all or atleast provides the wrong answer.

 

[Rick Rothstein]

Thats a very good suggestion, I did not consider trying that.

Having done that, it provides two of the three correct answers, it skips the first answer.
Super Brugsen, I am not too sure why, I even tried various permutations of adding a space to the arguments.

   If InStr(" " & UCase(sourcecell.Value) & " ", " " & UCase(approvedcell.Value) & " ") <> 0 Then

But to no avail, either it skips all or atleast provides the wrong answer.

The method cannot fail, so the only possibilities I can think of is either the value in the source cell is actually the approved cell and vice versa, or there is a spelling mistake somewhere... this could include one of the inside spaces being ASCII 32 (a normal space) and other being ASCII 160 (a non-breaking space that usually comes from copying data from a website).

 

[n35]

You are correct, in my attempt to fix things, I had removed the space between Super and Brugsen - brilliant, I will add this to my tips and tricks list thanks