Help with VBA: Copy not found value to another sheet

Hi guys,

I'm not very good at VBA, and I can't quite work out what I should be doing to modify this code to make it do what I want.

So, I have a massive index of documents (20,000+) each with a unique Doc ID. I need to work out which documents are in the list, and which are not. So my code so far is:

Please Login or Register  to view this content.

Essentially, it loops through the list in Sheet 2, and if it finds the relevant doc ID in Sheet 1, it copies it across to Sheet 3. That part works great. However, I now also need it to copy any doc IDs it doesn't find into Sheet 4. But that's the part I can't get it to do, and I don't know enough to figure it out.

This is the part that's failing:

Please Login or Register  to view this content.

Basically, if while it's looping through the search list in Sheet 2, and it doesn't find that value in Sheet 1, it should throw that search term into the next available row in Sheet 4.

Does that make any sense?

Thanks!

dName is going to be a number between 2 & srchLen

dName.Copy doesn't mean anything. What you need is a range. I think you need:

Please Login or Register  to view this content.

Thank you so much!

That works perfectly! And thank you for the explanation. I need to start trying to understand what I'm doing more.

Actually, before I attempt to do it myself, how would I get it to search either column A or column D in sheet 1? At the moment, I have the code duplicated, but I'm sure there must be a way to combine it.

You can but you would have to set it up differently. Right now you are looking for "Sheets(2).Range("A" & dName)" and using that value. The way it's written you would have to do it again using "D".

In fact I would probably rewrite the whole thing where you loop through the data once. If it exists it puts it on one sheet if not it puts it on the other sheet.

Ok, thanks. I'll see how I go trying to rewrite it. Thanks for your help!

Just in case anyone needed to do something similar, I thought I'd post my finished code here. Although bear in mind I'm an amateur, and I'm sure this could be done a lot better. I decided not to attempt an IF then ELSE as suggested above, simply because I needed results from column A and results from column D treated differently, and it was beyond my abilities to factor that in. So I kept it as I had it before. But I'm sure someone more capable could figure it out if they need to.

I have a huge index of documents in Sheet 1 (RationalisedIndex). Column A is a unique Doc ID. Column D contains additional comma separate IDs (Docs which reference other Docs). Columns B, C and E don't matter for this exercise (they have additional information on the document). I need to be able to search for multiple IDs at once. Sheet 2 (SearchList) is where I enter the list of IDs I need to search, with each ID in a separate row. Sheet 3 (FoundInIndex) and Sheet 4 (NotFoundInIndex) are results sheets. I need the search to do a few things:

1. Any existing search results in either Sheet 3 or Sheet 4 are cleared out before the new search is run
2. Search column A for all of the Doc IDs listed in Sheet 2. If one is found, copy the row into Sheet 3. Loop until the whole column is searched.
3. Search column D for all of the Docs IDs listed in Sheet 2. If one is found, copy the row into Sheet 3, and highlight it in yellow. Loop until the whole column is searched.
4. If a Doc ID is not found in either of column A or column D, copy the Doc ID into Sheet 4.
5. Display a message that the search is done.

So this is the code:

Please Login or Register  to view this content.