## Issue

The `Function TotalHours()`

is designed to accept two ranges. The first range should usually be one row by seven columns (seven days of the week) and the second range is a single cell to accept a value.

I have a problem setting the value for the one cell.

The line I’m having a problem with:

```
rCell.Value = sngOT
```

I get

"Application-defined or object-defined error".

I tried `rCell.Cells(1,1).Value = sngOT`

and get the same error.

```
Function TotalHours(myRange As Range, rOT As Range) As Single
Dim sngHours As Single, sngNormal As Single, sngOT As Single
Dim rCell As Range
sngHours = 0
sngNormal = 0
sngOT = 0
For Each rCell In myRange
If rCell.Value > 8 Then
sngOT = sngOT + rCell.Value - 8
sngNormal = sngNormal + 8
Else
sngNormal = sngNormal + rCell.Value
End If
Next rCell
If sngNormal > 40 Then
sngOT = sngOT + (sngNormal - 40)
sngNormal = 40
End If
sngHours = sngNormal + sngOT
Set rCell = rOT
rCell.Value = sngOT
Set rCell = Nothing
TotalHours = sngHours
End Function
```

## Solution

Like Scott Craner, I’m assuming the error is thrown as your function is being used as a UDF (else you wouldn’t get the error). Assuming that’s true, there is a way to change other cells from within a UDF.

The following does what you want.

Note: I’ve made other changes and added code notes as to why.

```
Function TotalHours(rgHours As Range, rgWriteOT As Range) As Single
''' Declare hour values as doubles
''' Note: doubles declares as zero
Dim dbTotalHrs#, dbNormalHrs#, dbOTHrs#, rgCell As Range
''' Get normal hours (max 8 per day) and bandwidth OT (i.e. any hours > 8 on any given day)
For Each rgCell In rgHours
dbNormalHrs = dbNormalHrs + WorksheetFunction.Min(rgCell, 8)
dbOTHrs = dbOTHrs + WorksheetFunction.Max(rgCell - 8, 0)
Next rgCell
''' For any given week worked more than 40 hours: All hours after 40 are OT
If dbNormalHrs > 40 Then
dbOTHrs = dbOTHrs + (dbNormalHrs - 40)
dbNormalHrs = 40
End If
''' Round results to 4 places (to avoid floating point rounding issues)
dbOTHrs = Round(dbOTHrs, 4)
dbTotalHrs = Round(dbNormalHrs + dbOTHrs, 4)
''' Return Total Hours
TotalHours = dbTotalHrs
''' Write OT Hours to rgWriteOT (via SetOTValue)
With rgWriteOT
.Parent.Evaluate "SetOTValue(" & .Address(False, False) & "," & dbOTHrs & ")"
End With
End Function
''' Sub to set a value in another cell
Sub SetOTValue(Target As Range, Value#)
Target = Value
End Sub
```

That all covered, important to point out, you could all of that easily enough with formulas:

Assuming your daily hours are in cells A2:G2, Total Hours in is Cell H2 and OT Hours is in I2, the following does what you want:

Formula for Total Hours (H2): `=SUM(A2:G2)`

Formula for OT Hours (I2): `{=IF(H2>40,H2-40,SUM(IF(A2:G2>8,A2:G2-8)))}`

Note the opening **{** and closing **}** at I2:

o This is an array formula. You don’t enter the **{** or **}**

o Rather, you ‘commit’ the formula using Ctrl, Shift and Enter

Answered By – Spinner

Answer Checked By – Marilyn (AngularFixing Volunteer)