Looking for help with .Find

[LookingforGuidance]

Hello all,

I am looking for a bit of help with the .Find function. I am trying to automate the process of checking for multiple duplicates but am getting stuck in 3 places that I am aware of at this point:

1. How do I tell my .Find to search within a specific column? Say, column B
- I know that I can pre-select my range with the following, but would like to do it within the .Find or with a variable if possible:

Workbooks(WorkbookToUse).Worksheets(MainWorksheet).Columns("B").Select

2. If I've found a match, how do I tell the search to find the next match in column B? I think I use an If statement and compare the results, but I don't want to loop around the bottom and start again from the top.

SearchAgain:
Cells.Find(What:=DataToFind, after:=ActiveCell, LookIn:=xlValues, _ LookAt:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, _ MatchCase:=False, SearchFormat:=False).Select

If ActiveCell.Value = DatatoFind then 
    
    'Send Results to Tab 2

    'Search again
    GoTo SearchAgain

Else if DataToFind = Null or End of Column reached then 
   
    'go to the next row in column A

End If

3. If I have reached the end of column B (matches or not), how do I stop without looping again back to the first match? This would fit into the Else If above. What I've used before is to check for the error message that a no-hit gives, but this seems to only work when looking for 1 duplicate and not multiple duplicates.



Again, I am just trying to process cells down column A against all of column B and if a duplicate is found, copy it to tab 2.

If anyone has any ideas but needs more information, I'd be glad to provide whatever I can.

Thank you,

LFG

 

[Marcelo Branco]

Maybe this can help

http://www.cpearson.com/excel/FindAll.aspx

M.

 

[LookingforGuidance]

Marcel,

Thank you so much! This is exactly the information that I have been looking for over the past 5 hours.

Thanks again,

LFG

 

[xenou]

Hi welcome to the board.

I wasn't aware of the FindAll method at chips site so Marcelo's suggestion looks great. To add some further responses:

You can use find against a column pretty much the same as with Cells. In the case of searching column B you could write:

Workbooks(x).Worksheets(y).Columns("B").Find

As far as continuing searching, there is a .FindNext method. I found this a bit tricky at first, when I learned about it for the first time. But there is an excellent example under the Find and FindNext articles in the VBA help files (repeated online here) so you can work through it several times until you understand it. That is, unless you find the earlier mentioned link has already solved the problem [Edit - oops...looks like it did already! Great!]

Find is an excellent method that comes in very handy but it's not entirely intuitive so be sure you've read the help files on it.


Regards,
ξ

 

[Marcelo Branco]

Marcel,

Thank you so much! This is exactly the information that I have been looking for over the past 5 hours.

Thanks again,

LFG

You are welcome!

Cheers