Using VBA Find smallest date larger than refdate for each lotid

[welchs101]

I have two columns of data (see enclosed file).

What i want to do is find the smallest date larger than the refdate (in cell E1) for each lotid. If there is no date larger than the refdate for a given lotid then return "n/a".

So ideally, i would have two output columns (see file.....cols H and I) where in col H i have the lotid and in col I have the smallest date larger than the refdate.

I want to do this via VBA.

Any ideas?

[tryer]

Hi Welchs, I don't understand what you mean by the smallest date larger than the ref date? maybe you can fill in some examples of the results you are looking for

[welchs101]

i am enclosing the same file but this timei am highlighting in yellow those dates, for a given lotid, which are smallest date larger than the date 9/1/11. Bascially, if there is a date for a given lotid larger than 9/1/11......the date that is largest that is closest to the 9/1/11 that is what i am looking for. does this answer your question?

[welchs101]

i came up with a soln........not sure if its the "best" approach but it works. see enclosed file for macro.