VBA define a cell value from a certain cell to a defined cell-CodePudding

I have been trying this back and forth for a while now, but I simply can't get this to work. I think I'm on to something with my latest code solution.

So what I want to achieve is simply put

A summed value from a daily tracker in cell F17 is to be added into a weekly tracker F24 on Monday if the date is Monday. Let's say I get the value 100 on Monday, I want this to be added to Monday in the weekly tracker. The next day I want the new value, let's say 50 to be added to Tuesday, but without changing the Monday value.

My current solution is to have a command button that when pressed adds the value to the correct day in the weekly tracker. My problem is that this code only works if F17 is an open input cell, so the value is not defined by a SUM function for example. But I want this to work with the cell that gets its value from a sum of other cells. Can someone please tell me what I am doing wrong here, so I could figure this out? I will add my current VBA code and some pictures to showcase.

Current code

Private Sub CommandButton1_Click()

Dim importValue As Double, i As Integer
importValue = Range("F15").Value


For i = 1 To 5
    If Weekday(Cells(23   i, 5).Value) = Weekday(Now) Then Cells(23   i, 6).Value = importValue
Next i

End Sub

What I want to happen

My current error code

CodePudding user response：

can you try this? The loop might not be necessary.

Private Sub CommandButton1_Click()

    Dim importValue As Double, i As Integer
    importValue = Range("F15").Value
    
    Cells(23   Weekday(Now()), 6).Value = importValue

End Sub