find certain word

[countryfan_nt]

Hello friends,

I have a column that has names. How do I look for the word "Sus" only? it is an abbreviation for a company name.
every time I look for that company I end up finding names like Susan, or Suswamy, etc. how can I make the finding for only SUS or Sus?

Thank you so much in advance!

 

[My Aswer Is This]

Try this to search for "Sus" in column "A"

Sub Test()
Application.ScreenUpdating = False
Dim i As Integer
Dim Lastrow As Long
Dim MyString As String
Lastrow = Cells(Rows.Count, "A").End(xlUp).Row
For i = 1 To Lastrow
MyString = Cells(i, 1).Value
If InStr(MyString, "Sus") > 0 Then MsgBox "Sus Was Found on Row  " & Cells(i, 1).Row
Next
Application.ScreenUpdating = True
End Sub

 

[L. Howard]

Something like this, then do what you want with the FOUND word.

Notice the xlWhole

Howard

Option Explicit

Sub Find_MyString()
Dim FindString As String
Dim Rng As Range
FindString = inputbox("Enter a Search value")
If Trim(FindString) <> "" Then
    With Sheets("Sheet1").Range("A:A") 'searches all of column A
        Set Rng = .Find(What:=FindString, _
                        After:=.Cells(.Cells.Count), _
                        LookIn:=xlValues, _
                        LookAt:=[B]xlWhole[/B], _
                        SearchOrder:=xlByRows, _
                        SearchDirection:=xlNext, _
                        MatchCase:=False)
        If Not Rng Is Nothing Then
            MsgBox Rng & " Is in cell: " & Rng.Address
            Application.Goto Rng, True 'value selected
        Else
            MsgBox "Nothing found" 'value not found
        End If
    End With
End If
End Sub

 

[hiker95]

How do I look for the word "Sus" only? it is an abbreviation for a company name.

countryfan_nt,

To start off:

1. Can we see examples of the strings that contain Sus?

2. What is the worksheet name that contains the abbreviation's?

3. What column are they in?

4. What is the first cell in the column that contains the abbreviation's?

 

[My Aswer Is This]

Sorry. My script will find Susisme. Learning just like you.

Try this to search for "Sus" in column "A"

Sub Test()
Application.ScreenUpdating = False
Dim i As Integer
Dim Lastrow As Long
Dim MyString As String
Lastrow = Cells(Rows.Count, "A").End(xlUp).Row
For i = 1 To Lastrow
MyString = Cells(i, 1).Value
If InStr(MyString, "Sus") > 0 Then MsgBox "Sus Was Found on Row  " & Cells(i, 1).Row
Next
Application.ScreenUpdating = True
End Sub

 

[hiker95]

every time I look for that company

countryfan_nt,

1. What version of Excel, and, Windows are you using?

2. Are you using a PC or a Mac?

3. How are you looking for that company?

4. Are you presently using a formula?

4a. If so, what is the formula?

5. Are you presently using a macro?

5a. If so, can we see your macro code?

When posting VBA code, please use Code Tags - like this:

[code=rich]

'Paste your code here.

[/code]

 

[Matty]

Hello friends,

I have a column that has names. How do I look for the word "Sus" only? it is an abbreviation for a company name.
every time I look for that company I end up finding names like Susan, or Suswamy, etc. how can I make the finding for only SUS or Sus?

Thank you so much in advance!

You would need to 'pad' your lookup value with a space at either side. Can you post a small example of your data so that we can see what's going on?

Matty