[baygins]

I have a workbook with 2 worksheets. First one will make use of a user-defined function using data from the 2nd worksheet.
The functions are at the bottom of the post.

There is a user-defined function in cell C13 in "Sheet1", which is passed a string parameter ("Sheet2") that instructs the function to do the calculations in a range in the sheet whose name is passed in the parameter.
What happens, is that when I call the "CalculateHours" function, I cannot seem to get Excel to select the correct worksheet using the "Worksheets("Sheet2").Range(Cells (1,1), Cells(10,1))" form.
Instead, Excel uses "Sheet1" contents when it comes to the WITH block, although the parameter clearly points to "Sheet2".

I am going crazy here; and the cell below it (C14) uses the "Other" function that arbitrarily assigns a value using the same notation. But this one WORKS!

Can someone please let me know why the Worksheets("WorksheetName") format is not picking up the correct workhseet in my "CalculateHours" code?

Thanks,
Selim

Public Function FindEmptyRow(WorkSheetName As String, ColNo As Integer)
Dim counter As Integer
counter = 1
For Each c In Worksheets(WorkSheetName).Columns(ColNo).Cells
If Len(c.Value) > 0 Then
counter = counter + 1
End If
Next
FindEmptyRow = counter
End Function

Public Function CalculateHours(Person As String, PersonColNo As Integer, wksWorkSheetName As String, EmptyCheckColNo As Integer, HoursColNo As Integer, Category As String, DescMatchColNo As Integer, Billable As Boolean, BillColNo As Integer)
' This function calculates the hours a person has recorded under different categories.
' Parameter List:
' ----------------------------------------------------------------------------------------------------
' Person as String Hours are going to be calculated for this person whose name
' will be matched in the "PersonColNo" column of the data range
' PersonColNo as Integer Column number in the worksheet that has the person's name
' WorkSheetName as String Active worksheet's name where the calculation wil be done.
' EmptyCheckColNo as Integer Column number in the worksheet where the function will search for the
' first non-empty cell to determine the search range.
' HoursColNo as Integer Column number in the worksheet where the function will sum up
' hours reported for the employee.
' Category as String Time category that needs to be summed for reporting.
' DescMatchColNo As Integer Column number where the function will try to match category name to
' description field contents.
' Billable As Boolean Whether billable hours should be summed up or not.
' BillColNo As Integer Column number where the billable hours flag is found. "Yes" or "No"
'

Dim NoRows, counter, Hours As Integer
' Dim c As c
Hours = 0
NoRows = 0
NoRows = FindEmptyRow(wksWorkSheetName, EmptyCheckColNo)
counter = 2
With Application.ActiveWorkbook.Worksheets(wksWorkSheetName)
.Activate
For Each c In .Range(Cells(2, PersonColNo), Cells(NoRows - 1, PersonColNo))
If c.Value = Person Then
If (Billable) Then
' Do the calculation
If Cells(counter, BillColNo).Value = "Yes" Then
Hours = Hours + Cells(counter, HoursColNo)
End If
Else
If InStr(Cells(counter, DescMatchColNo).Value, Category) > 0 Then
Hours = Hours + Cells(counter, HoursColNo)
End If
End If
End If
Next
End With
CalculateHours = Hours
End Function


Public Function Other()
Dim dummy As Integer, returnvalue As String
Worksheets("Sheet2").Activate
dummy = 0
returnvalue = Worksheets("Sheet2").Cells(3, 3).Value
Other = returnvalue
End Function