Increase a cell value by the date

I have a sheet that imports hours and date to a spreadsheet similar to the attached. The imported info reflects the current total hours worked in the week.

After I import the file with the current weekly hours, I manually enter the current hours on the sheet that reflects the previous day that were worked. The formula does the math to give the result of the hours worked of the previous day. The imported info is a current and increasing weekly total from day to day.

Is there a code that would compare the dates then enter the respective daily hours on the cell that reflects the hours of the stipulated date?

Thank you, Robert
dates.xlsx
Who is Participating?

Commented:
Normally, you would rather use the Select statement in place of so many Ifs. But in this case I would use a loop and an array like this:-
``````Dim i as Integer
Dim Arr() As Variant
Arr = Array(17, 18, 19, 110, 111, 112, 113)
For i = 7 To 13
If Range("14").Value = Range("C" & i).Value Then Exit For
Next i
If i <= 13 Then
Range(CStr(Arr(i -7)).Value = Range("D4").Value
End If
``````
0

EngineerCommented:
=LOOKUP(1000000000000000,F5:F11)
0

Commented:
If the imported number of hours that go into E3 increase every day, i.e. they represent the sum of all hours worked during the week no worksheet formula can save you from the job of transferring that number to the appropriate row in the range E5:E11 manually. This is because if one were to compare the dates, as you suggest, the row to enter the current number could be found but the previous day's number would be lost when the new day's number is written. A solution of this problem would require code.
However, one might look at your import procedure. Perhaps it is possible to write the number directly to the appropriate cell in E5:11. Very likely, the same argument would make it impossible. However, seeing that E3:F3 are quite superfluous, if you were to employ code you should employ it for the import of the data rather than their processing.
0

Author Commented:
Thank you for your response, what would be the code on the import sheet?
0

Commented:
I don't know.
You wrote:-
I have a sheet that imports hours and date to a spreadsheet similar to the attached. The imported info reflects the current total hours worked in the week.
So, if your sheet "imports hours and date" you must refer to the data in E3:F3. How do you import them?
0

Author Commented:
Guys, thank you for your responses, I probably did a poor job of communicating my question, apologies.
I went a little direction, I put this code into a macro button and it is working for me.

Private Sub updt_Click()
If Range("I4") = Range("C7") Then Range("I7") = Range("D4")
If Range("I4") = Range("C8") Then Range("I8") = Range("D4")
If Range("I4") = Range("C9") Then Range("I9") = Range("D4")
If Range("I4") = Range("C10") Then Range("I10") = Range("D4")
If Range("I4") = Range("C11") Then Range("I11") = Range("D4")
If Range("I4") = Range("C12") Then Range("I12") = Range("D4")
If Range("I4") = Range("C13") Then Range("I13") = Range("D4")
End Sub

Should I reward the points to both who offered suggestions. Please let me know.
Thank you,
Robert
0

EngineerCommented:
If the other comments did not help and yours is the only working solution then you should accept your own solution
0

Author Commented:
Thank you guys
0

Author Commented:
Faustulus, I thought I had your formula working but it is producing an error. Would you mind to see it on sheet3?
Thank you.
dates.xlsm
0

Commented:
I'll look at your workbook as soon as I get to my PC.
0

Commented:
I wrote that code on an iPad. Indeed, I forgot to specify the column in the target range. I had also referred to Range("14") instead of Range("I4").The following code corrects the errors. It also refers to Range("E4") instead of Range("I4").
``````Private Sub Worksheet_Activate()

Dim i As Integer
Dim Arr() As Variant

Arr = Array(17, 18, 19, 110, 111, 112, 113)
For i = 7 To 13
If Range("E4").Value = Range("C" & i).Value Then Exit For
Next i
If i <= 13 Then
Range("I" & CStr(Arr(i - 7))).Value = Range("D4").Value
End If
End Sub
``````
The above code also suggests to use the Worksheet_Activate event Observe the procedure's declaration). The Selection_Change event you have been using occurs every time you click anywhere in teh worksheet. This may cause unexpected results simply because you tend to forget that the code is replacing the same total so many times each day. Choosing the Activate event will reduce the number of times the code is run, but perhaps it isn't suitable for your work flow.
The Worksheet_Change event isn't suitable because the change occurs in another sheet. However, if the change is made manually there you could capture the event on that sheet and let it run the same code you now have on this sheet - I hope you can follow. Perhaps there is a change you make daily somewhere in the workbook that you can harness as a trigger.
There are two alternatives. One is to limit the action to occur not on every click but only when a certain cell or range of cells is clicked. The other is to set a variable that prevents the action to be taken more than once.That variable would be lost when Excel is shut down, so that the procedure can run again the next day. It could also be reset by another even in the workbook. The point is that you want the procedure to run when it needs to run but stop it from repeatedly doing the same job.
0

Author Commented:
Thank you, Faustulus, you are right the activate method is better.

And it works great.

Robert
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.