VBA Code to Find a Whole Word in a Statement

[OilEconomist]

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.

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]

 

[jasonb75]

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 ",

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]

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

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]

@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]

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.

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