I am trying to compare a sheet with claims (with from and through
dates) to another sheet with amounts applicable to a different and
potentially overlapping set of date ranges.

In Sheet 1, each record consists of a claim ID, a member ID, and the
from and thru dates.


In Sheet 2, there is a Member ID, from and thru dates (more than one
set of dates per member), and an applicable dollar amount associated
with that date range.


What I am trying to do is to show the dollar amount in Sheet1 for
each
claim that matches with the date ranges shown in Sheet 2.


Although my ultimate goal is to prorate (by day) the amounts in Sheet
2 to any instances where there is a match in Sheet 1, I am trying to
figure out how to first pull in the amounts from Sheet 2 in all
instances where there is a match with the dates in Sheet 1.


Is there VBA code that could compare the date range from sheet 2 to
the date range in sheet 1 (for a particular member) and then produce
the monthly amount in any instance where there is a match between the
two date ranges?

Any suggestions would be greatly appreciated.


So, to show the example.


Sheet 1 has:
Claim ID Member ID From Date Thru Date Amount
a33 555-333 4/1/05 5/30/05 PULL
FROM SHEET 2 IF MATCH
b22 312-444 3/7/07 6/15/08 PULL
FROM SHEET 2 IF MATCH
e44 312-444 7/7/08 8/15/08 PULL
FROM SHEET 2 IF MATCH
c12 767-575 etc etc


Sheet 2 has:
Member ID From Date Thru Date Amount
555-333 4/7/05 5/30/05 50
312-444 3/7/07 6/15/08 75
767-575 etc etc 200