Change Cell Value in Single Cell Range-CodePudding

I'm missing something here and I just can't figure it out.

Function 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'm specifically having a problem setting the value for the one cell.

The line I'm having a problem with: rCell.Value = sngOT

I keep getting an "Application-defined or object-defined error".

I've tried using 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

Any help appreciated.

Thanks, Westley

CodePudding user response：

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