Search Text Excel Macro

[JustATheory]

Greetings,

I need to write a macro that will search a column of cells in that have about two sentences each with a word (Hello) embeded somewhere with in that text, identify which cells have the text and place a mark ("X") in the cell next to it and continue searching until the activecell = "". Not every cell has this text. It's similar to the simple "Find" function, but I need to identify each cell that contains this text.

Any help is greatly appreciated.

Thanks,
Andy

 

[firefytr]

Hi there,

You can something like this ...

Sub MarkAll()
    Dim c As Range, rngLoop As Range
    Set rngLoop = Sheets("Sheet1").Range("A1:A100")
    For Each c In rngLoop
        If InStr(1, UCase(c.Value), "HELLO") <> 0 Then
            c.Offset(0, 1).Value = "X"
        End If
    Next c
End Sub

Change out the sheet name and range for your specifics. This does not check for case sensitivity; if that matters to you, remove the surrounding UCase() and change the "HELLO" to the case sensitivity you desire.

Also, it assumes "cell next to it" means one cell to the right.

HTH

-----------
Regards,
Zack Barresse

 

[xlbo]

straight from the help files:

With Worksheets(1).Range("a1:a100")
    Set c = .Find("HELLO", lookin:=xlValues, lookat:=xlpart)
    If Not c Is Nothing Then
        firstAddress = c.Address
        Do
            c.offset(0,1).value = "X"
            Set c = .FindNext(c)
        Loop While Not c Is Nothing And c.Address <> firstAddress
    End If
End With

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question

 

[JustATheory]

Thanks to both firefytr and xlbo, for some great code. Both code sets work well and accomplised my task.

Thanks,
Andy

 

[firefytr]

For large data sets, Geoff's code will run much faster.

I thought about throwing one of those routine's together, guess I just got lazy. :?

-----------
Regards,
Zack Barresse

 

[xlbo]

possibly - I just remembered that the FindNext example in help is very good and easily modded !

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question