Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations strongm on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

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

Status
Not open for further replies.

JustATheory

IS-IT--Management
Feb 27, 2003
115
US
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
 




Hi,

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

Skip,

[glasses] [red][/red]
[tongue]
 
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
 
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

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

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886
 
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
 
Thanks Geoff,


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

Andy
 
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
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top