InStr as part of an array that contains multiple texts

Alroj

New Member
Joined
Jan 12, 2016
Messages
20
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.

VBA Code:
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
 

Excel Facts

Create a chart in one keystroke
Select the data and press Alt+F1 to insert a default chart. You can change the default chart to any chart type

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
79,611
Office Version
  1. 365
Platform
  1. Windows
How about
VBA Code:
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

New Member
Joined
Jan 12, 2016
Messages
20
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

VBA Code:
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

MrExcel MVP
Joined
Feb 23, 2021
Messages
5,590
Office Version
  1. 365
Platform
  1. Windows
Why did you put the word Newtown in this line:
VBA Code:
If InStr(1, cells(r, "Newtown"), x) > 0

Expecting that line to say:
VBA Code:
If InStr(1, cells(r, "B"), x) > 0

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

bebo021999

Well-known Member
Joined
Jul 14, 2011
Messages
1,938
Office Version
  1. 2016
Something like this:
VBA Code:
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
 
Solution

Alroj

New Member
Joined
Jan 12, 2016
Messages
20
Why did you put the word Newtown in this line:
VBA Code:
If InStr(1, cells(r, "Newtown"), x) > 0

Expecting that line to say:
VBA Code:
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"

1661228854520.png





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

New Member
Joined
Jan 12, 2016
Messages
20
Something like this:
VBA Code:
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

MrExcel MVP
Joined
Feb 23, 2021
Messages
5,590
Office Version
  1. 365
Platform
  1. Windows
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.
 

Forum statistics

Threads
1,181,228
Messages
5,928,770
Members
436,628
Latest member
Krakenfan69

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top