Look for a value and clear it

[Dan R.]

I'm trying to find anything in column 9 that = "-//200-" and clear it.
Why does this not work?

iEnd = ws.Cells(65536, 9).End(xlUp).Row
Set rng = ws.Range(ws.Cells(3, 9), Cells(iEnd, 9))
For Each c In rng
If InStr(c, "-//200-") = False Then c.Clear
Next c

Thanks,
-- Dan

 

[Dan R.]

oops, that should be:

iEnd = ws.Cells(65536, 9).End(xlUp).Row
Set rng = ws.Range(ws.Cells(3, 9), Cells(iEnd, 9))
For Each c In rng
If InStr(c, "-//200-") = True Then c.Clear
Next c

 

[Chip Pearson]

Change

If InStr(c, "-//200-") = True Then c.Clear

to

If InStr(c, "-//200-") > 0 Then c.Clear

True has a numeric value of -1, and InStr returns 0 or a positive integer
indicating the position at which the string was found. It will never
return -1. For example


Dim P As Integer
P = InStr(1, "ABC", "B")
Debug.Print "P = True: " & (P = True), "P = " & P


You'll see that P is not equal to True.


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com
(email address is on the web site)

 

[Dan R.]

Excellent. Thanks Chip, I appreciate the explanation.

-- Dan