Need help with cells.find and do if looping

[wellsw]

I have been trying to adapt the following code

Please Login or Register  to view this content.

What I need it to do is look only in column C for cells that are not = to "#N/A"
currently it finds N/A's in Multiple columns and returns an entry for each.

I realize the union code is not required for this example but it will be for other iterations of the macro

[Leith Ross]

Hello wellsw,

Try this...

Please Login or Register  to view this content.

[6StringJazzer]

The reason that the Find is looking in all cells, and not just column C, is that is what is specified. The code specifies

Please Login or Register  to view this content.

and Cells is a reference to the entire worksheet. You have used

Please Login or Register  to view this content.

but then the code never actually takes advantage of that With. To do that you want something like this:

Please Login or Register  to view this content.

The .Find means "Do a find on the range I have specified in my With statement."

However, that doesn't solve your problem I just am trying to do a little education there. The bigger problem is that a Find can only find a string, it cannot find cells that fail to match a string. That would have to be done as an explicit loop. I have suggested one solution here. You will also note that I declare all variables, which I highly recommend (along with Option Explicit, which is automatically provided in new modules if you set Tools, Options, Require Variable Declaration).

I have tested this code on your file and I believe it does what you want:

Please Login or Register  to view this content.

[wellsw]

6StringJazzer
That is exactly what I needed... and thanks so much for breaking it down for me... that makes it easier to understand so I can adopt it for other applications!!