find text within string and then delete

[scubadiver]

I want to look for the word 'report' within a larger text string and then
delete all the text in the cell.

 

[Mike H]

Hi,

This works on column A. Right click your sheet tab, View code and paste this
in and run it.

Sub stance()
Dim MyRange As Range
Lastrow = Cells(Cells.Rows.Count, "A").End(xlUp).Row
Set MyRange = Range("A1:A" & Lastrow)
For Each c In MyRange
If InStr(1, UCase(c.Value), "REPORT") Then
c.ClearContents
End If
Next
End Sub

Mike

 

[Joel]

Also this

With Sheets("Sheet1")
Set c = .Cells.Find(what:="REPORT", LookIn:=xlValues, lookat:=xlPart)

If Not c Is Nothing Then
firstAddress = c.Address
Do
c.ClearContents
Set c = .FindNext(after:=c)
Loop While Not c Is Nothing And c.Address <> firstAddress
End If
End With

 

[scubadiver]

thanks.

I didn't need all the code but it helped.

 

[Rick Rothstein]

If InStr(1, UCase(c.Value), "REPORT") Then

The InStr function has two optional arguments... the first which is a
starting position for the search (InStr is an unusual function in its having
an optional *first* argument) and, if the first argument is specified, a
fourth which allows you to make it perform a non case sensitive search. So,
using your UCase approach as shown above, the function call could have been
simplified to this...

If InStr(UCase(c.Value), "REPORT") Then

but using the first and fourth arguments would allow us to eliminate the
UCase function call...

If InStr(1, c.Value, "Report", vbTextCompare) Then

where the letters in the word "Report" can be in any case (here I use the
more friendly looking, at least to me, "proper" case).