Seaching a Single Column without Selecting

[boylejob]

How can I take the code listed below and do a search on a single column. In part of the sub I might want to seach Col B and in another part of the sub I might want to search Col J. What do I need to do to this so it will limit the search to a particular column.

Please Login or Register  to view this content.

[jindon]

Change

Please Login or Register  to view this content.

to

Please Login or Register  to view this content.

[boylejob]

Thank you! I'll give that a try. There are some days it is easier to ask than search and test for an hour.

I appreciate the help.

[mikerickson]

This example should show you how to build what you want.
The routine looks through searchColumn looking for the string SearchFor.
They could be passed to your routine as arguments.

The address of the first cell is noted, so that the loop can test when .Find loops around to the top of the range again.

Notice that the Do Loop is inside With EndWith, so that searchColumn applies to .FindNext. Also note that .FindNext needs a starting point (the most recently found cell).

By avoiding selection, this can be run from when a differnent sheet is active

Please Login or Register  to view this content.

[boylejob]

mikerickson,

Please Login or Register  to view this content.

Can the "A:A" of .Range("A:A") be set to a variable. I always seem to have trouble with that, but that is actually what I need. If it can be set to a variable, how do I do it.

[mikerickson]

searchColumn is a variable.

Having the Sub take arguments is one way to control it. Four lines would be removed and the declaration would be changed.
The start of the sub would look like

Please Login or Register  to view this content.

And then you could call the routine like

Please Login or Register  to view this content.

Note that all occurances of "Dorothy" is being found in both columns D and E. The search can be restricted to any continious range.

[broro183]

hi all,

Tushar's Findall function at the below link has a little more depth than what you are after but you may still find it useful...

http://www.tushar-mehta.com/excel/tips/findall.html

hth
Rob