Hello,

I have a worksheet called "FY04 Requests" which records
per row a training request by an individual. There can be
many training requests by one individual. I'm trying to
figure out how I can write a function to show the total
value per individual and per quarter of approved requests
on a different worksheet.

I currently have what's below. But for some reason the
first .Range(B2:K100) call keeps on returning nothing. The
Function is defined in a module next to some recorded
Macros.

Function Calculate_Used_Budget_By_Quarter(FullName As
String, Quarter As String) As Double
'
' Calculate_Used_Budget_By_Quarter Function
' Added by Wim Bartsoen on 27/10/2003
'
' Purpose: This function calculates the total training
' budget used by a member of the specialty by doing
' an SQL like query on the FY04 Requests worksheet.
' The following parameters are passed to it:
' - Name of the requestor
' - Quarter of the request (accepted arguments are Q1,
Q2, Q3 and Q4)
'
' Dependencies:
' * The function takes the whole of the sheet
entitled "FY04 Requests"
' as its database
' * The keyword that needs to be matched is "Approved"
' * It assumes the amounts to sum are in Column J
' * It assumes that the date to compare is in Column G
' * It makes some assumptions about the sheet layout to
improve speed

Const SearchParam1 As String = "Approved"

Dim StartDate As Date
Dim rng As Range
Dim Sum As Double

Sum = 0
rng = ActiveWorkbook.Worksheets("FY04 Requests").Range
("B2:K100")
For n = 1 To rng.Rows.Count
If rng.Cells(n, 2).Value = "Approved" _
And rng.Cells(n, 3).Value = FullName Then
Sum = Sum + rng.Cells(n, 10).Value
End If
Next
Calculate_Used_Budget_By_Quarter = Sum

End Function

Can someone point me at what is going wrong here?

Any help is greatly appreciated.

Wim