To address this, I would create a user defined function with the
phone_number, start_date and end_date as parameters passed to the UDF.

I assume that the call costs are on a separate sheet which we can call BILL.

you will need to create a function called say, mobile_costs

e.g. Function mobile_costs(number, start, end)
the function will need to access the sheet BILL
(workbooks("Bill_book").activate;sheets("BILL").se lect)
Start at the first data row, (range("A2").select)
commence a loop (do until activecell.value="")
check if the phone number is the same as number, (if
activecell.value=number then)
If so, then check if the call date is equal to or greater than start
(if activecell.offset(0,1)=start then)
If so, check if the call date is less than or equal to end (if
activecell.offset(0,2)<= end then)
If so, add the call cost to a running total (running_total =
running_total + activecell.offset(0,3).value)
end if
end if
move down one row (selection.offset(1,0).select)
loop
mobile_cost=running total (assigns the value of running total to the
function)
End Function

this is of course untested, and in effect a very linear solution, but should
work for you. I would ensure that you sort by nu mber, start date, end date
before opening the list of clients.

HTH

Steve

"J P Singh" <noemail@asIhatespam wrote in message
...
Hi All

Can someone please help me put a solution in place for this problem.

We have two lists of data which we need to match.

We loan out phones to our business users who use them for fixed number of
days and then return them to us. This data is stored in one worksheet as

Mobile Number Date From Date To User Prod Number
07855422282 12/03/2003 13/03/2003 Kiersty Smith 33089320
07855422282 23/04/2003 27/04/2003 Kiri Belcher 33097720
07855422282 19/06/2003 20/06/2003 Tim Day 10130610
07855412282 01/07/2003 05/07/2003 Jackie O'Shea 33453410
07855412282 07/08/2003 09/08/2003 Rhodi Evans 334616 ETW
07855412282 14/01/2003 16/01/2003 Andrew Orr 901101103


Once the users have used the phones we get a bill from our phone company
data in which looks like

Phone No Date Total Cost
07855422282 12/3/03 1.54
07855422282 13/3/03 1.20
07855422282 23/4/03 0.58
07855422282 24/4/03 1.45
07855422282 25/4/03 0.00
07855422282 26/4/03 0.96
07855422282 27/4/03 1.99
07855412282 07/8/03 1.54
07855412282 08/8/03 2.58
07855412282 09/8/03 0.58
07855412282 14/1/03 1.29
07855412282 15/1/03 2.66

What I need to do is to find out what was the total cost of calls for a
particular user for the time he has had the phone. I am sure this can be
done through VBA but not sure how to start.

Then results should look something like
Mobile Number Date From Date To User Total Cost
07855422282 12/03/2003 13/03/2003 Kiersty Smith £2.74
07855422282 23/04/2003 27/04/2003 Kiri Belcher
07855422282 19/06/2003 20/06/2003 Tim Day
07855412282 01/07/2003 05/07/2003 Jackie O'Shea
07855412282 07/08/2003 09/08/2003 Rhodi Evans
07855412282 14/01/2003 16/01/2003 Andrew Orr



Can you offer some help please.

Thanks