VBA Code to Find a Whole Word in a Statement

OilEconomist

Board Regular
Joined
Dec 26, 2016
Messages
226
Office Version
  1. 2019
Platform
  1. Windows
Thanks in advance and feedback will be given on any suggestions.

How do I VBA code to find the whole word. For example: The sentence "Dog Fight" I will find the necessary word "Dog" , but if I'm searching for "D" I only want to find it if the sentence explicitly states it. In the case now, it finds "D" in Dog Fight.

Code:
Sub Sorter()
'_____________________________________________________________________________________________

'Turn off alerts, screen updates, and automatic calculation
    'Turn off Display Alerts
        Application.DisplayAlerts = False

    'Turn off Screen Update
        Application.ScreenUpdating = False
         
    'Calculate to ensure all values are updated
       Calculate

   'Turn off Automatic Calculations
        Application.Calculation = xlManual

'_____________________________________________________________________________________________
'Dimenisoning/Declaring Variables
    Dim LastRow As Long
    Dim Stock As String
    Dim WS_Names As Worksheet
    Dim WS_MWD As Worksheet
    Dim Rw As Long
    Dim i As Long
    Dim RangeFind As Range



'_____________________________________________________________________________________________

'Set the worksheet names
    Set WS_Names = Sheets("Sheet1")
    Set WS_MWD = Sheets("Sheet2")



'_____________________________________________________________________________________________
'Find the last row of data in Sheet1 Sheet
    'Activate the sheet first
        WS_Names.Activate
     
    'Find the last row
        LastRow = Cells.Find(What:="*", After:=Range("A1"), lookat:=xlPart, _
            LookIn:=xlFormulas, SearchOrder:=xlByRows, SearchDirection:=xlPrevious, _
            MatchCase:=False).Row



'_____________________________________________________________________________________________

'Set the Rw number to start with
    WS_Names.Range("A3").Activate

'Loop through and match up the data
    For i = 2 To LastRow
        'Activate Sheet 1
               WS_Names.Activate
      
        'Check to see if either the stock ticker or company name is in a cell
            If Range("A" & i).Value = "" Then
                'Move on to the next cell
             
            Else
                'Set the cell value to the string
                    Stock = Range("A" & i).Value

                'Activate Sheet2 to perform the search
                    'Activate the sheet
                        WS_MWD.Activate

                    'Set the search range and perform the search
                        Set RangeFind = Range("A:A").Find(Stock, LookIn:=xlValues, lookat:=xlWhole)

                'If the value is not found
                    If RangeFind Is Nothing Then
                        'Do nothing

                'If the value is found

                    Else
                        If Range("H" & RangeFind.Row).Value = "" Then
                            Range("H" & RangeFind.Row).Value = Stock
                   

                            Else
                                Range("I" & RangeFind.Row).Value = Stock
                                              

                        End If                  

                End If

          End If

    Next i



'Turn on alerts, screen updates, and calculate
        'Turn On Display Alerts
            Application.DisplayAlerts = True

        'Turn on Screen Update
            Application.ScreenUpdating = True

        'Turn off Automatic Calculations
            Calculate

'Place the curser in cell A2 of the "Name.Range" Worksheet
    WS_MWD.Activate
    Range("A2").Select


End Sub

[1/Code]
 

Some videos you may like

Excel Facts

Control Word Wrap
Press Alt+Enter to move to a new row in a cell. Lets you control where the words wrap.

jasonb75

Well-known Member
Joined
Dec 30, 2008
Messages
12,448
Office Version
  1. 365
Platform
  1. Windows
For example: The sentence "Dog Fight" I will find the necessary word "Dog"
I think you might have posted the wrong code, your example is set to find an exact match for the whole sentence, so it would only compare "Dog Fight" to "Dog Fight", "Dog", "Fight", "D", or anything else would not match.

Regardless of that, the normal method would be to search for " D " in " Dog Fight ",
VBA Code:
If Instr(" " & "Dog Fight" & " ", " " & "D" & " ") Then
Replacing "Dog Fight" and "D" with references to the relevant cells. To do this you would have to look through the cells in the column to be searched, it will not work with the range.find method.
 

Yongle

Well-known Member
Joined
Mar 11, 2015
Messages
6,977
Office Version
  1. 365
Platform
  1. Windows
To test
Insert code below in a new module
Run TestFunc
Enter the text to be found in the input box

- if it works, incorporate into your procedure, otherwise feedback here

VBA Code:
Sub TestFunc()
    On Error Resume Next
    Sheets("Sheet2").Activate
    ActiveSheet.Cells(GetRow(InputBox("enter search text")), 1).Activate
    On Error GoTo 0
End Sub
Private Function GetRow(SearchStr As String) As Long
    Dim arr, p, rng As Range, r As Long, aStr As String
    Const S = " "
    SearchStr = S & SearchStr & S
    arr = [A1].CurrentRegion.Resize(, 1)
    
    For r = 2 To UBound(arr)
        aStr = S & arr(r, 1) & S
        For Each p In Array(".", ",", ";", ":", "?", "!")
            aStr = Replace(aStr, p, S)
        Next p
        If InStr(1, aStr, SearchStr, vbTextCompare) > 0 Then
            GetRow = r
            Exit Function
        End If
    Next r
End Function
 

OilEconomist

Board Regular
Joined
Dec 26, 2016
Messages
226
Office Version
  1. 2019
Platform
  1. Windows
@jasonb75 and @Yongle apologies for never responding, but I could never get my code to work so I shelved it for a while. I am going to eventually try to determine the issue. I think between the words there are some type of characters that appear to be spaces.
 

Rick Rothstein

MrExcel MVP
Joined
Apr 18, 2011
Messages
36,868
Office Version
  1. 2010
Platform
  1. Windows
Here is a function I wrote some time ago that will find the position of a word (could be a phrase) by itself (that is, not embedded within a larger word). For your use, you could set the cell value as the first argument and the word you want to find as the second argument... if it returns 0, then the word does not stand alone within the text, whereas if it returns a number, then the word is in the text as a stand alone word (and is located at at the character position within the text for the number returned by my function should you ever need to know that). Note the two optional arguments... they should be self-explanatory.
VBA Code:
Function InStrExact(ByVal SearchText As String, ByVal FindMe As String, _
                    Optional ByVal Start As Long = 1, _
                    Optional ByVal MatchCase As String = False) As Long
  Dim X As Long, Str1 As String, Str2 As String, Pattern As String
  If MatchCase Then
    Str1 = SearchText
    Str2 = FindMe
    Pattern = "[!A-Za-z0-9]"
  Else
    Str1 = UCase(SearchText)
    Str2 = UCase(FindMe)
    Pattern = "[!A-Z0-9]"
  End If
  For X = Start To Len(Str1) - Len(Str2) + 1
    If Mid(" " & Str1 & " ", X, Len(Str2) + 2) Like Pattern & Str2 & Pattern Then
      InStrExact = X
      Exit Function
    End If
  Next
End Function
 

Watch MrExcel Video

Forum statistics

Threads
1,127,519
Messages
5,625,273
Members
416,086
Latest member
CaptainGD

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