Usually Array Formula but Function needed

[David2e]

I'm just getting into functions, so please bear with me on this as I start to understand how they really work. I do have the VBA and Macros for MS Excel book which has been a great help!

Normally on this I would use a simple array formula to gather a total calculation from a timesheet, but in this case there can be numerous timesheets to add this up.

Hours would be recorded on a sheet with both a client and a task selected. I also have a summary sheet to show clients against tasks... adding all the hours from each timesheet.

If it were from a single Timesheet (possibly creating a single one of these for all sheets is an option?) I would be able to use the following array formula:

=SUM(IF((Timesheet1!$F$8:$F$22=C$5)*(Timesheet1!$G$8:$G$22=$A9),Timesheet1!$D$8:$D$22))

Timesheet1 Column D = hours
Timesheet1 Column F = clients
Timesheet1 Column G = tasks
Row 5 (starting Column C) = clients in summary sheet (across top)
Column A (starting Row 9) = tasks in summary sheet (down side)

Although timesheets can have rows added, this could be handled. Unfortunately it's the number of timesheets that's the issue as this is a variable... though another worksheet handles this so the number of these and their names are also controlled and can be used in formula (eg. in a loop to go through all). The names change (not sequentially numbered) but these are all kept in a named list in the workbook.

I started to approach this as a function along the lines...

=TotalHours(client,task)

where client would be picked up from the top (Row 5) and task from the side (Column A).

and then use this function (bearing in mind this is possibly my first function)

Function TotalHours(TheCat1 As String, TheCat2 As String) As Integer

Dim AddHours As Integer

AddHours = 0

For Each cell In Range("AllCat3")

allRows = Sheets(cell).Range("D65536").End(xlUp).Row - 10

For i = 1 To allRows

CurrRow = 8 + i

If TheCat1 = Sheets(cell).Cells(5, CurrRow) And TheCat2 = Sheets(cell).Cells(6, CurrRow) Then
AddHours = AddHours + Sheets(cell).Cells(4, CurrRow).Value
Else: End If

Next i

Next cell

TotalHours = AddHours

End Function

AllCat3 is the range for the timesheets and containing the worksheet names.
The first 8 rows of a timesheet are extra info, as are the last two rows (for picking up the actual number of rows used for recording time.

What am I doing wrong here? At the moment I am tossing up maintaining an extra timesheet that stores data from all the others... though I suspect this would be an inefficient way of doing this.

Any direction would be appreciated.

Thanks

David

 

[David2e]

Hi Glenn

The current code is just as I posted above, but with the Application.Volatile included.

Thanks

David

 

[GlennUK]

Hi Glenn

The current code is just as I posted above, but with the Application.Volatile included.

Thanks

David

You have said that you removed the "As Integer". There are 2. Which did you remove, or both? I wouldn't have had to ask this if you'd have posted the code.

 

[David2e]

Hi Glenn

Sorry about that - I wasn't wanting to bloat out this thread, but you've pointed me again, to my own silly error.

My first function which is now working, but had the Function also as integer which I hadn't noticed. I've changed this to 'single' now and am a little more aware of what to look for.

Thanks for your time, you've pointed me in the right direction every time so I do appreciate your help.

Cheers

David

 

[GlennUK]

Hi David,

glad you got it working.