asked on

need a macro to find the 2 letter word in a cell

i have 500000 rows on one column

i need a macro to identify which cells have the word "at" in them. BUT NOT IF ITS PART OF A LARGER WORD. just the word "at"

Shums Faruk

Hi try below, which will add helper column to distinguish if word "at" appears between two space:

Sub FilterRng2()
Dim Ws As Worksheet
Dim LR As Long
Dim FindString As String
Dim Rng As Range
Set Ws = ActiveSheet
LR = Ws.Range("A" & Rows.Count).End(xlUp).Row
With Application
    .ScreenUpdating = False
    .DisplayStatusBar = True
    .StatusBar = "!!! Please Be Patient...Updating Records !!!"
    .EnableEvents = False
    .Calculation = xlManual
End With

Ws.Range("B2:B" & LR).FormulaR1C1 = "=IF(ISNUMBER(SEARCH("" at "",RC1)),""Yes"", ""No"")"

Set Rng = Ws.Range("B1:B" & LR)
If Rng.AutoFilter = True Then
    Rng.AutoFilter
    Rng.AutoFilter Field:=1, Criteria1:="Yes", Operator:=xlAnd
End If
With Application
    .ScreenUpdating = True
    .DisplayStatusBar = True
    .StatusBar = False
    .EnableEvents = True
    .Calculation = xlAutomatic
End With
End Sub

Open in new window

ASKER CERTIFIED SOLUTION

Martin Liss

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Rgonzo1971

Hi,

As a function to determine if here is a "at" alone
pls try

Function fIsAtPresent(Myrange As String) As Boolean
    Dim regex As Object
    Set regex = CreateObject("VBScript.RegExp")
    Dim strInput As String
    strInput = Myrange
    With regex
        .Global = True
        .MultiLine = True
        .IgnoreCase = True
        .Pattern = "\bat\b"
    End With
    bRes = regex.Test(strInput)
    fIsAtPresent = bRes
    Set regex = Nothing
End Function

Open in new window

Regards

Martin Liss

I'm glad I was able to help.

If you expand the “Full Biography” section of my profile you'll find links to some articles I've written that may interest you.

Marty - Microsoft MVP 2009 to 2016
Experts Exchange MVE 2015
Experts Exchange Top Expert Visual Basic Classic 2012 to 2016