VBA to put a calculated value into another cell as a static value once per day upon opening file.

[MB110]

I have a calculated field that averages a range of data based on certain conditions. I need a record of this dynamic value to be captured once a day or week, doesn't really matter, when the spreadsheet is opened.

My dynamic calulated cell is O4 and the place I want to put is in a table adjacent to the date on another tab.

Any help is greatly appreciated,

mb110

 

[MARK858]

I want to put is in a table adjacent to the date on another tab

What range are the dates in on the other sheet?

 

[MB110]

What range are the dates in on the other sheet?

The place to put the value is in a table on another tab and the date are to be a weekly update....
lets say every Monday when the spreadsheet is opened.

So the code, which fires when the spreadsheet is open, needs to first check the table to see if the current date is already there along with the value from O4 on the "Demurrage Tracking" tab. If it's not then add the current date (Column A) to the next open row and the value from O4 into (Column B).

Lets call the tab to move to "Duration Goal Data" where Column A is called "ActDate" and Column B is called "AvgAct". Below is the table thus far. It uses a weekly check each Monday, however is being manually updated and I want it automated. This new table is the record source for my line chart with trend line.

ActDate AvgAct
04/22/19 47
04/29/19 45
05/06/19 43

If need be I can populate the date field for the entire year and the code can check to see if current equals the next date in the table that doesn't have a value in column B. I suppose the code can also look at the the max date in the table, add 7 days, and then populate it to the next open row in Column A.

I appreciate the help. I'm a decent Excel user but I can't write VBA. I'm more of a search find copy paste edit programmer..lol.

mb110

 

[MARK858]

Try the code below, code goes in the ThisWorkbook module.

Private Sub Workbook_Open()
    Dim Findcll As Range
    Set Findcll = Sheets("Duration Goal Data").Cells.Find(CDate(Date), , xlValues, , xlByRows, xlNext)
    If Findcll Is Nothing Then
        Sheets("Duration Goal Data").Cells(Rows.Count, "A").End(xlUp)(2) = Date
        Sheets("Duration Goal Data").Cells(Rows.Count, "A").End(xlUp)(1, 2) = Sheets("Demurrage Tracking").Cells(2, "O").Value
    Else
        Findcll.Offset(, 1).Value = Sheets("Demurrage Tracking").Cells(2, "O").Value
    End If
End Sub

 

[MARK858]

If you only want the code to update the first time the workbook is opened on the day then try the slightly amended code below.
Code still goes in the ThisWorkbook module.

Private Sub Workbook_Open()
    Dim Findcll As Range
    Set Findcll = Sheets("Duration Goal Data").Cells.Find(CDate(Date), , xlValues, , xlByRows, xlNext)
    If Findcll Is Nothing Then
        Sheets("Duration Goal Data").Cells(Rows.Count, "A").End(xlUp)(2) = Date
        Sheets("Duration Goal Data").Cells(Rows.Count, "A").End(xlUp)(1, 2) = Sheets("Demurrage Tracking").Cells(2, "O").Value
    Else
        If Findcll.Offset(, 1).Value = "" Then Findcll.Offset(, 1).Value = Sheets("Demurrage Tracking").Cells(2, "O").Value
    End If
End Sub

 

[MB110]

It works well except it doesn't get the value from cell O4 on the Demurrage Tracking. The line below I don't fully understand.

Sheets("Demurrage Tracking").Cells(2, "O").Value

I changed it to this and it works but I would like to learn what your code is doing and where it failed. Maybe what I did will fail under certain conditions. I just don't fully understand VBA syntax.

Sheets("Demurrage Tracking").[O4].Value

 

[MARK858]

It works well except it doesn't get the value from cell O4 on the Demurrage Tracking.

That is because it was my error, for some reason I had it in my head it was O2 and not O4
It should have been

Sheets("Demurrage Tracking").Cells([COLOR="#FF0000"]4[/COLOR], "O").Value

but I would like to learn what your code is doing and where it failed

Does the above make it clearer?

 

[MB110]

Perfect! I did suspect the code was referencing a row count and I changed the 2 to a 4 and it still didn't work. I tried it again and this time it did work. I guess I had something else amiss.

Anyway, it is working exactly as I envisioned it would. Thank you very much for your expertise and explanations so I could learn. You are awesome!

Have a great day!
mb110

 

[MARK858]

Just for your reference the Cells syntax takes either the column letter (as in the code posted) or the column number which makes it useful for assigning to variables for looping etc. so you can do things like...

Sub yyyy()
    Dim i As Long
    For i = 1 To 20 Step 2
        Cells(1, i) = "aaaa"
    Next
End Sub