InStr as part of an array that contains multiple texts

[Alroj]

Hi MrExcelers,

I have adapted this VBA that has been helpful with finding the first word included in a string within each cell in my sheet. However, if the word is in the middle of a string the word is not identify. I think the function "Instr" may help but I have been unable to adapt this function in the macro. You'll see that I have included the function "InStr" in the array (in bold) below but it is not working. I would appreciate much if any of you could assist please.

Sub WordInAString()

    Dim lr As Long
    Dim arr
    Dim r As Long
    Dim i As Long
    Dim x As String
 
    Application.ScreenUpdating = False

    [B]arr = Array("ABC", "10ABC", InStr(cell.Value, "ABC") > 0)[/B]
'   Find last row with data in column A
    lr = cells(Rows.Count, "A").End(xlUp).Row
 
'   Loop through all rows from bottom to top
    For r = lr To 1 Step -1
'       Loop through each value in array and check for a match
        For i = LBound(arr) To UBound(arr)
'           Get value to look for
            x = arr(i)
'           Check for value
            If Left(cells(r, "B"), Len(x)) = x Then
'               If value found, write the category assigned
             cells(r, "B").Offset(0, 7).Value = "Newtown Public School"
                Exit For
            End If
        Next i
    Next r

    Application.ScreenUpdating = True
 
    MsgBox "Macro complete!"

End Sub

 

[Fluff]

How about

Sub WordInAString()

    Dim lr As Long
    Dim arr
    Dim r As Long
    Dim i As Long
    Dim x As String
 
    Application.ScreenUpdating = False

    arr = Array("ABC", "10ABC")
'   Find last row with data in column A
    lr = Cells(Rows.Count, "A").End(xlUp).Row
 
'   Loop through all rows from bottom to top
    For r = lr To 1 Step -1
'       Loop through each value in array and check for a match
        For i = LBound(arr) To UBound(arr)
'           Get value to look for
            x = arr(i)
'           Check for value
            If InStr(1, Cells(r, "B"), x) > 0 Then
'               If value found, write the category assigned
             Cells(r, "B").Offset(0, 7).Value = "Newtown Public School"
                Exit For
            End If
        Next i
    Next r

    Application.ScreenUpdating = True
 
    MsgBox "Macro complete!"

End Sub

 

[Alroj]

Fluff,

thank you very much for your assistance. Unfortunatelly, it didn't work.
I replaced the texts included in the array and the text included in the InStr function to use real words and it gave me a "Run-time error '13': Type mismatch"
Not sure what has failed

Sub WordInAString()

    Dim lr As Long
    Dim arr
    Dim r As Long
    Dim i As Long
    Dim x As String
 
    Application.ScreenUpdating = False

    arr = Array("0Newtown", "1Newtown")
'   Find last row with data in column A
    lr = cells(Rows.Count, "A").End(xlUp).Row
 
'   Loop through all rows from bottom to top
    For r = lr To 1 Step -1
'       Loop through each value in array and check for a match
        For i = LBound(arr) To UBound(arr)
'           Get value to look for
            x = arr(i)
'           Check for value

'  Here is where the VBA stops with a message "The Run-time error '13': Type mismatch"
            If InStr(1, cells(r, "Newtown"), x) > 0 Then

'               If value found, write the category assigned
             cells(r, "B").Offset(0, 7).Value = "Public School"
                Exit For
            End If
        Next i
    Next r

    Application.ScreenUpdating = True
 
    MsgBox "Macro complete!"

End Sub

 

[Alex Blakenburg]

Why did you put the word Newtown in this line:

If InStr(1, cells(r, "Newtown"), x) > 0

Expecting that line to say:

If InStr(1, cells(r, "B"), x) > 0

With x being one of the values defined in the line arr = Array("0Newtown", "1Newtown")

 

[bebo021999]

Something like this:

Sub WordInAString()
    Dim lr As Long
    Dim r As Long
    Dim i As Long
    Application.ScreenUpdating = False
'   Find last row with data in column A
    lr = cells(Rows.Count, "A").End(xlUp).Row
'   Loop through all rows from bottom to top
    For r = lr To 1 Step -1
            '  Use "like * text *" to find partial match
            If LCase(cells(r, "B")) like "*newtown*"  Then   cells(r, "I").Value = "Public School"
    Next r
    Application.ScreenUpdating = True
    MsgBox "Macro complete!"
End Sub

 

[Alroj]

Why did you put the word Newtown in this line:

If InStr(1, cells(r, "Newtown"), x) > 0

Expecting that line to say:

If InStr(1, cells(r, "B"), x) > 0

With x being one of the values defined in the line arr = Array("0Newtown", "1Newtown")

Hi Alex,

Column B contains the information with the word "newtown" that I want to categorise as "public school". The Array in the VBA helps to recognise only some of them but not when the word "newtown" is in the middle or the end of the sentence. So I included in the array a couple of them and tried to pick up the other ones with the function InStr so all of them can be categorised as "public school"

The member Bebo021999 has also sent me a solution. I just tested it and it worked very well.
Thank you for your attention Alex, I'll be using Bebo021999's solution.

 

[Alroj]

Something like this:

Sub WordInAString()
    Dim lr As Long
    Dim r As Long
    Dim i As Long
    Application.ScreenUpdating = False
'   Find last row with data in column A
    lr = cells(Rows.Count, "A").End(xlUp).Row
'   Loop through all rows from bottom to top
    For r = lr To 1 Step -1
            '  Use "like * text *" to find partial match
            If LCase(cells(r, "B")) like "*newtown*"  Then   cells(r, "I").Value = "Public School"
    Next r
    Application.ScreenUpdating = True
    MsgBox "Macro complete!"
End Sub

Hi Bebo021999, Thank you for your assistance. Your solution did the trick !
Much appreciated!

 

[Alex Blakenburg]

Hi Alex,

Column B contains the information with the word "newtown" that I want to categorise as "public school". The Array in the VBA helps to recognise only some of them but not when the word "newtown" is in the middle or the end of the sentence. So I included in the array a couple of them and tried to pick up the other ones with the function InStr so all of them can be categorised as "public school"

Thanks for the feedback. Glad you got what you needed.