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

MB110

New Member
Joined
Mar 24, 2019
Messages
29
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
 

Excel Facts

Copy a format multiple times
Select a formatted range. Double-click the Format Painter (left side of Home tab). You can paste formatting multiple times. Esc to stop
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?
 
Upvote 0
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
 
Last edited:
Upvote 0
Try the code below, code goes in the ThisWorkbook module.

Code:
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
 
Upvote 0
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.

Code:
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
 
Upvote 0
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.

Code:
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.

Rich (BB code):
Sheets("Demurrage Tracking").[O4].Value

 
Upvote 0
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 :oops:
It should have been

Code:
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?
 
Upvote 0
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
 
Upvote 0
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...

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

Forum statistics

Threads
1,213,527
Messages
6,114,150
Members
448,552
Latest member
WORKINGWITHNOLEADER

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top