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

thanks a lot steve

I have managed to get it working however as the billing data has about 12000
rows scanning each row has made the macro very slow.

Any advice on refining this


Sub CalcTotal()
Dim TempPh
Sheets("sheet2").Select
i = 2
Do Until Cells(i, 1) = ""
TempPh = Cells(i, 1).Value
startval = Cells(i, 2).Value
endval = Cells(i, 3).Value
' MsgBox ("TempPh = " & TempPh)
total = mobile_costs(TempPh, startval, endval)
Cells(i, 6).Value = total
' MsgBox ("Total = " & total)
i = i + 1
Loop
End Sub


Private Function mobile_costs(number, start1, end1)
running_total = 0
Sheets("Sheet1").Select
Range("A2").Select
found = False
Do Until ActiveCell.Value = ""
If ActiveCell.Value = number Then
startrow = ActiveCell.Row
found = True
Exit Do
End If
Selection.Offset(1, 0).Select
Loop
If found = False Then
MsgBox ("Number not found in the list")
End If

Do While ActiveCell.Value = number
If ActiveCell.Value = number Then
If ActiveCell.Offset(0, 1) = start1 And ActiveCell.Offset(0, 1) <= end1
Then
running_total = running_total + ActiveCell.Offset(0, 2).Value
' MsgBox ("Start Date : " & start1 & vbCrLf & "Enddate : " & end1
& vbCrLf & "Curentdate : " & ActiveCell.Offset(0, 1) & vbCrLf & "Total : " &
running_total)
End If
End If

' MsgBox (ActiveCell.Value)
Selection.Offset(1, 0).Select
Loop

mobile_costs = running_total
Sheets("sheet2").Select

End Function

"Steve Smallman" wrote in message
...
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

If I was going to do it and you found the array formula too slow for the
large number of formula, I might explore having a macro put the array
formula in each row, one at a time, perform a calculate, then replace the
formula with the value returned or use the evaluate command in your code to
calculate the array formula in code, then place the value in a cell. Or an
alternative might include use of the subtotal worksheet function in
conjunction with an autofilter. I find it often best to use Excel's built
in methods, but I could be out of place here and looking at Steve's
masterful suggested use of the activate and select methods, dutifully
implemented by you, perhaps you need to work with him. As a minimum, I am
sure he would suggest an Application.ScreenUpdating = False at the top and
Application.ScreenUpdating = True at the bottom to lessen the visual impact
of the code.

--
Regards,
Tom Ogilvy



"J P Singh" <noemail@asIhatespam wrote in message
...
thanks a lot steve

I have managed to get it working however as the billing data has about
12000
rows scanning each row has made the macro very slow.

Any advice on refining this


Sub CalcTotal()
Dim TempPh
Sheets("sheet2").Select
i = 2
Do Until Cells(i, 1) = ""
TempPh = Cells(i, 1).Value
startval = Cells(i, 2).Value
endval = Cells(i, 3).Value
' MsgBox ("TempPh = " & TempPh)
total = mobile_costs(TempPh, startval, endval)
Cells(i, 6).Value = total
' MsgBox ("Total = " & total)
i = i + 1
Loop
End Sub


Private Function mobile_costs(number, start1, end1)
running_total = 0
Sheets("Sheet1").Select
Range("A2").Select
found = False
Do Until ActiveCell.Value = ""
If ActiveCell.Value = number Then
startrow = ActiveCell.Row
found = True
Exit Do
End If
Selection.Offset(1, 0).Select
Loop
If found = False Then
MsgBox ("Number not found in the list")
End If

Do While ActiveCell.Value = number
If ActiveCell.Value = number Then
If ActiveCell.Offset(0, 1) = start1 And ActiveCell.Offset(0, 1) <=
end1
Then
running_total = running_total + ActiveCell.Offset(0, 2).Value
' MsgBox ("Start Date : " & start1 & vbCrLf & "Enddate : " &
end1
& vbCrLf & "Curentdate : " & ActiveCell.Offset(0, 1) & vbCrLf & "Total : "
&
running_total)
End If
End If

' MsgBox (ActiveCell.Value)
Selection.Offset(1, 0).Select
Loop

mobile_costs = running_total
Sheets("sheet2").Select

End Function

"Steve Smallman" wrote in message
...
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