VBA - Loop through array & use search function on string

[Webbarr]

Hoping someone can help, I'm having an issue with my code. I'm using an error handler so not able to clarify the error itself.

Sheet 1 -

This is the list that I am attempting to categorise with my worksheetfunction.search, as my data is sensitive I have decided to use my breakfast as the search words!

[TABLE="width: 500"]
<tbody>[TR]
[TD]A[/TD]
[TD]B[/TD]
[/TR]
[TR]
[TD]Phrase[/TD]
[TD]Food[/TD]
[/TR]
[TR]
[TD]I will have bacon for breakfast[/TD]
[TD]bacon[/TD]
[/TR]
[TR]
[TD]I will have sausages for breakfast[/TD]
[TD]sausages[/TD]
[/TR]
[TR]
[TD]I will have bacon and sausages for breakfast[/TD]
[TD]multiple[/TD]
[/TR]
</tbody>[/TABLE]

Sheet 2 -

This is my array.

[TABLE="width: 500"]
<tbody>[TR]
[TD]A[/TD]
[/TR]
[TR]
[TD]Food[/TD]
[/TR]
[TR]
[TD]bacon[/TD]
[/TR]
[TR]
[TD]sausage[/TD]
[/TR]
</tbody>[/TABLE]

In short - The macro is designed to look at the string from Sheet 1, Column A (excluding heading obviously), and find a match via a worksheetfunction.search in Sheet 2, Column A. If both matches are positive it is to display "Multiple" as seen in my category column of sheet 1. If no match is found I will use "No match".

My issue - my first string "I will have bacon for breakfast" matches bacon, perfect. My 2nd string "I will have sausages for breakfast" finds no matches. My 3rd string "I will have bacon and sausages for breakfast" returns "multiple", again perfect.

My code -

Sub srch()


Dim tbl As ListObject
Dim ws1 As Worksheet, ws2 As Worksheet
Dim tarStr As String, list() As String


Set ws1 = Worksheets("Data")
Set ws2 = Worksheets("Lookups")


Set tbl = ws2.ListObjects("Table1")


i = tbl.Range.Rows.Count - 1


ReDim list(i)


For j = 1 To i
    list(j) = ws2.Range("A" & j + 1).Value
Next j


ws1.Activate
ws1.Range("A2").Activate


Do While ActiveCell.Value <> ""
    tarStr = ActiveCell.Value
    On Error Resume Next
    For j = 1 To i
        strSrch = WorksheetFunction.Search(list(j), tarStr, 1)
            If Err = 0 Then
                x = x + 1
                cat = list(j)
            End If
            If j = i And x <> 1 And cat <> "" Then
                cat = "multiple"
            End If
            If j = i And x <> 1 And cat = "" Then
                cat = "no match"
            End If
    Next j
    ActiveCell.Offset(1, 0).Activate
    x = 0
    cat = ""
Loop


End Sub

Thanks in advance for any help!

 

[Peter_SSs]

For clarity, if the word "bacon" for example occurred twice (or more) in a single cell, should the result be "bacon" or "multiple"?

 

[Peter_SSs]

You also need to be careful with just matching text. For example, if one of your foods was "apple" and on sheet 1 you had "I ate pineapple for breakfast", a standard text search would return "apple" which I doubt you would want.

Is there any punctuation in the Sheet 1 text values?

 

[Webbarr]

Hi Peter,

Both very valid points. If the term bacon occurs more than twice in any given string it should still remain as bacon, also the pineapple/apple question does indeed raise more questions. I'll have to think of a workaround for that.

In relation to your final statement, there is no punctuation in the Sheet 1 text values, nor Sheet 2.

 

[Peter_SSs]

.. also the pineapple/apple question does indeed raise more questions. I'll have to think of a workaround for that.

Indeed. I hadn't actually noticed with your first post, but it is an issue with your "sausage"/"sausages" texts too. You may want "sausage" to be treated as found in that text but what if the text was, say, "I had sausagemeat made into meatballs". What would be the rule/logic of counting one but not the other?

 

[Webbarr]

Going by that idea, I would have to add "meatballs" into my array which should return multiple. The actual dataset I'm playing with is an ever expanding list of invoices. We currently have an agreed upon list of references to report on, around the 100 mark, but a multiple would have to be dealt with manually.

It may also be worth noting that as of yet the "cat" variable has not been told to input the value into the corresponding cell, I've only been testing it in the held memory.

 

[Peter_SSs]

Going by that idea, I would have to add "meatballs" into my array ..

I was really just emphasising the issue of what you are going to do when/if one of your search terms turns up as a subset of a longer word. In the end, any code is going to have to implement some sort of 'rule' to determine what to do.

It may also be worth noting that as of yet the "cat" variable has not been told to input the value into the corresponding cell, I've only been testing it in the held memory.

To be honest, I have taken virtually no notice of your existing code as I'm waiting till I have a clear understanding of what the logical 'rules' are in relation to the things discussed above above.