VBA to input dates based on weekly hours

[Bamacoatie]

Good afternoon,
I am trying to figured out a VBA to input two different things.
1. One code to input a sum of hours to a approximate number (90) +/- 4. (i.e. P6:P12 sums to 88, P13:P20 sums to 93 etc) and input that number into column R next to the last number that is summed.

2. second code is to input dates for each week that sums up to the approximate 90 hours per week. (i.e. first ~90 hours is completed between 6-21-2021 to 6-27-2021, at the end of each summed week that is in column R is the end of that given week added in column T. And the next cell in the T column is that given Monday)

attached is a picture of what I do manually now.
I apologize if this has been asked already.
Thanks for any help.

 

[severynm]

For your first problem, a simple loop should suffice. Sum the values until the sum is 90±4, record that value in the next row over, and then reset the sum and repeat.

Private Sub Sum90Hours()
    Dim hours As range
    Dim sum As Double
    Dim cell As range
    Set hours = Me.range("D1:D54")
    
    For Each cell In hours
        sum = sum + cell.Value
        If sum > 86 And sum < 94 Then
            cell.Offset(0, 1).Value = sum
            sum = 0
        End If
    Next cell
End Sub

The next part you could just use a formula for

=IF(E2>0,F2+7,F2)

where col E is Sum of Date and F is the previous date (or however you have your table set up - the screenshot does not specify). You'd have to set up the very first week manually, but other than that it'll be ok. If you'd rather that be controlled with VBA too, that should be a relatively simple add on.

 

[Bamacoatie]

For your first problem, a simple loop should suffice. Sum the values until the sum is 90±4, record that value in the next row over, and then reset the sum and repeat.

Private Sub Sum90Hours()
    Dim hours As range
    Dim sum As Double
    Dim cell As range
    Set hours = Me.range("D1:D54")
   
    For Each cell In hours
        sum = sum + cell.Value
        If sum > 86 And sum < 94 Then
            cell.Offset(0, 1).Value = sum
            sum = 0
        End If
    Next cell
End Sub

The next part you could just use a formula for

=IF(E2>0,F2+7,F2)

where col E is Sum of Date and F is the previous date (or however you have your table set up - the screenshot does not specify). You'd have to set up the very first week manually, but other than that it'll be ok. If you'd rather that be controlled with VBA too, that should be a relatively simple add on.

Code works pretty good. Just had to tweak some of the columns because I didn’t disclose the right ones.
I’m going to have to play around with it and the formula to fit what i want.
thanks for all the help.