return missing values

[Guerolito]

Hi,

I have two worksheets every with two column.
In the first sheet in Column A are 4digit codes, in Column B should be filled in required information(missing indexes) .
Sheet 2 is based on report for these 4 digit codes(column A) with existing indexes for these codes.
The values for the indexes are between 1-9999 and they were not used sequentially.
For example for code 1685 we can see in sheet2 that used indexes are 1,2,5,30,998 so in sheet 1 should be returned 3,4,6.

Can you please suggest vba code that return unused indexes?

Regards,
Guerolito

[jindon]

You didn't mention how you want the result.

Please Login or Register  to view this content.

[Guerolito]

Thank you Jindon,

The results should be returned in sheet1 column "Index".

Regards,
Guerolito

[jindon]

Can you show me how exactly you want the result?

[Guerolito]

Result should looks like as in attached example file - sheet1 column index.

Regards,
Gguerolito

[jindon]

You know the result in message box is exactly the same as what is in Sheet1.

So, no need to change Sheet1.

Is that what you want?

[Guerolito]

I think we are almost there but probably I didn’t explain correctly situation so I’ll try again.
In the example file I filled in Sheet1 “index” with the required indexes manually in order to explain requirements better, but normaly at the beginning I have only codes in Sheet1, index column is blank.
I filter codes using Advanced filter in order to get unique codes. Then I run report for this unique codes to find already used indexes. The result of report is in Sheet2.
And then I need VBA code to return in Sheet1 column “Index”
indexes which have not been used yet.

Regards,
Guerolito

[jindon]

What do you mean by

indexes which have not been used yet.

[Guerolito]

indexes which have not been used yet = These are figures between 1-9999 which not exist in combination with respective Code in Sheet2

Regards,
Guerolito

[jindon]

You mean like this?

Please Login or Register  to view this content.

[Guerolito]

Thank you jindon
It's very close to the solution.
Just one remark.
Is it possible not to change the returned indexes after every execution of code.
I mean if there is no changes in workbook to return the same indexes after every execution.

Regards,
Guerolito

[jindon]

Just delete one line before End Sub which is

Please Login or Register  to view this content.

[Guerolito]

PERFECT!
It seems that works as just like I want.
Now I should understand how the code works.
Again Thank you!
Guerolito