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

 

[GlennUK]

I haven't looked at your logic yet, but this looks odd:
Cells(5, CurrRow)

as the first argument when using cells is the row number. You have put a variable named as if it's for rows in the column number argument place.

 

[David2e]

Thanks Glenn

Yes, an error I had missed... but it didn't lead to it working.

I'm trying to run it under a normal macro, but it seems to get held up with "Sheets(cell)" so I'm still trying to figure that one out.

Been a bit confusing, although new to functions I would have thought this would work. I'll just keep trying but I am wondering if I'm going about it the right way, or if this will be a slow inefficient function.

Thanks

David

 

[GlennUK]

Show your code now that you think that you've fixed errors. And explain what you mean by

but it seems to get held up with "Sheets(cell)"

What happens exactly?

 

[Mala]

maybe:
Sheets(cell.Text)

 

[David2e]

Thanks

That was it (cell.text)... plus a small error in the reference but picked that up easily with the testing.

The final working code is:

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.Text).Range("D65536").End(xlUp).Row - 10

For i = 1 To allRows

CurrRow = 8 + i

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

Next i

Next cell

TotalHours = AddHours

End Function

The only thing is it seems to need to recalculate to work (ie. entering cell and hitting return again), although automatic calculate is turned on. Will look into what can be done (if anything) or even if a 'calculate now' button might be needed.

Many thanks!

 

[GlennUK]

Put Application.Volatile in your code for your function to be recalculated at calculation time.

 

[Mala]

Like:

Function TotalHours(TheCat1 As String, TheCat2 As String) As Integer
Application.Volatile
Dim AddHours As Integer
(remaining code)....
....
End Function

 

[David2e]

Thanks everyone!

I've got that working now with the calculate. Seems a bit touchy, sometimes updated automatically, other times needing F9 (usually after load it seems).

The issue I'm actually struggling with now is the function seems to be automatically rounding the figure. I removed the 'as integer' but it's still rounding it which I'm finding a little odd. Have I missed something, or not understood how it's working?

Thanks

David

 

[GlennUK]

Thanks everyone!

I've got that working now with the calculate. Seems a bit touchy, sometimes updated automatically, other times needing F9 (usually after load it seems).

The issue I'm actually struggling with now is the function seems to be automatically rounding the figure. I removed the 'as integer' but it's still rounding it which I'm finding a little odd. Have I missed something, or not understood how it's working?

Thanks

David

Show us your current code.