InStr Function - singling out specific words

[lockarde]

Good morning all,

I've read sesveral similar posts, but nothing pertains to my exact case so I figured I'd make a new post and see what you think. I'm trying to sort through a database of products, each has one or more tags associated with it. I'm using InStr function to match a word with a tag and then copy that product to its respective sheet. Right now it works almost exactly how I want it - the issue is when a tag shares a word with another. For instance, if I search for " Tent" the search also picks up products whose tag says " Tent Accessory", and I don't want this. Is there a way to make InStr more specific, and ONLY search for the word I'm looking for? Or maybe another function or search method would work better? My code is below:

    With Sheets("Variant Information")
    
    i = 1
    lastRow = .Cells(.Rows.Count, "A").End(xlUp).Row
        
        Do While i < lastRow + 1
            str = Sheets("Variant Information").Cells(i + 1, 12).Value 'grabs the tag for searching
            arc = Sheets("Variant Information").Cells(i + 1, 9).Value  'checks if product has been archived
            
            If InStr(UCase(str), " SLEEPING BAG") = 1 And InStr(arc, "NO") = 1 Then 'looks for all sleeping bags that have not been archived
                With Sheets("Sleeping Bags")
                    Last = .Cells(.Rows.Count, "A").End(xlUp).Row
                    .Cells(Last + 1, 1).Value = Sheets("Variant Information").Cells(i + 1, 2).Value
                    .Range(.Cells(4, 1), .Cells(8000, 1)).NumberFormat = "0"
                End With
            ElseIf InStr(str, " Pack") = 1 And InStr(arc, "NO") = 1 Then
                With Sheets("Backpacks")
                
                    Last = .Cells(.Rows.Count, "A").End(xlUp).Row
                    .Cells(Last + 1, 1).Value = Sheets("Variant Information").Cells(i + 1, 2).Value
                    .Range(.Cells(4, 1), .Cells(8000, 1)).NumberFormat = "0"
                
                End With
            ElseIf InStr(str, " Taped") = 1 And InStr(arc, "NO") = 1 Then
                With Sheets("Taped Jackets")
                
                    Last = .Cells(.Rows.Count, "A").End(xlUp).Row
                    .Cells(Last + 1, 1).Value = Sheets("Variant Information").Cells(i + 1, 2).Value
                    .Range(.Cells(4, 1), .Cells(8000, 1)).NumberFormat = "0"
                    
                End With
            ElseIf InStr(str, " Tent") = 1 And InStr(arc, "NO") = 1 Then
                With Sheets("Tents")
                    Last = .Cells(.Rows.Count, "A").End(xlUp).Row
                    .Cells(Last + 1, 1).Value = Sheets("Variant Information").Cells(i + 1, 2).Value
                    .Range(.Cells(4, 1), .Cells(8000, 1)).NumberFormat = "0"
                End With
            End If
            
            i = i + 1
        
        Loop

    End With

 

[RoryA]

Assuming you don't need to worry about punctuation, add a space before and after the string you are searching, and to the value you are searching for. That should then only match on whole words.

 

[DanteAmor]

You can expand the word, for example:

ElseIf InStr(str, " Tent tools") = 1 And InStr(arc, "NO") = 1 Then

But I guess you already did.
Another alternative is to eliminate those you do not want, for example

ElseIf InStr(str, " Tent") = 1 And InStr(arc, "NO") = 1 Then
    if instr(str, "[COLOR=#333333]Accessory") = 0 then

[/COLOR]                With Sheets("Tents")
                    Last = .Cells(.Rows.Count, "A").End(xlUp).Row
                    .Cells(Last + 1, 1).Value = Sheets("Variant Information").Cells(i + 1, 2).Value
                    .Range(.Cells(4, 1), .Cells(8000, 1)).NumberFormat = "0"
                End With    
   end if

 

[lockarde]

DanteAmor, this should work perfectly! Thanks so much for the reply. I had a "Doh!" moment, adding new checks is the easiest way for sure!

 

[DanteAmor]

I'm glad to help you. Thanks for the feedback.