Loop looking for text that "Does Not Contain" a value Excl VBA

[JustATheory]

Greetings,

Please review the thread I've mentioned below. I use Geoff's example with great success, but I'm looking now for something to do the opposite. For instance, look for records that do not contain "Hello". Thanks in advance for your assistance.

Thanks,
Andy

thread707-1225330

Example

With Worksheets(1).Range("A:A")
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

 

[SkipVought]

Hi,

Have you tried the AutoFilter -- critera does not contain???

Skip,

_[red][/red]

 

[PHV]

A starting point:
For Each c In Worksheets(1).Range("A:A")
If UCase(c.Value) Like "*HELLO*" Then
c.Offset(0, 1).Value = "X"
End If
Next

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[JustATheory]

Hi Skip,
I use the autofilter regularly, for this case I need a routine once I find the cells not containing "Hello", I then need to have a function and it is embeded in a macro.

Thanks,
Andy


Hi PH,

I'm actually looking for cells that do not contain "Hello", I tried to add "Not" before the "Like" and am getting an error message. What would the operator be in this case?

Thanks,
Andy

 

[PHV]

OOps, sorry:
If Not UCase(c.Value) Like "*HELLO*" Then

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[xlbo]

Could still use autofilter - 10 to 1 it'll be faaaaaaar quicker than looping unless you aren't checking that many records

Filter for Does Not Contain "Hello" as Skip says and then you can loop through the (much smaller) VisibleCells collection to work with just the cells that don't contain "Hello". Once you have marked them or done whatever, simply remove the autofilter et voila

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


That does work well also. Thanks for all the input it's extremely helpful.

Andy

 

[fumei]

akakuris, please consider using the Thank Tek-Tip member for this valuable post.

If the thread has been "extremely helpful" for you, not only is is good to to show this in a concrete way, but it also marks the thread as possibly helpful for others.

faq219-2884

Gerry
My paintings and sculpture