VBA Code for recording value changes if it is great or less than the current minimum

[gearhead29]

Hi

So I am working on a workbook to track my cars mileage over its finance term. I have developed a sheet that predicts its future mileage and the point where the car will be back on target for its predicted annual mileage.

On an area of the sheet there are 4 cells in a column

The first is a calculated cell based on the trend-line of the results, where it provides a date output varying as predictions are converted to real world recorded values, this works on formulas just fine.

The cell directly bellow that is the value of the previous prediction point from before a change is made, ie value when the sheet was last closed/saved, currently entered manually.

The next two cells I want to record the max value ever predicted and the minimum value predicted to give an idea of whether the dates are advancing or receding, so these should only be changed if the calculated value is greater than the max value cell or less than the minimum value cell.

I have tried to create a formula to do this however haven't had much luck, so far I have been entering all these values manually but have met a few human error issues with this so any ideas appreciated. see example bellow which has the correct cell and row references.

[TABLE="width: 500"]
<tbody>[TR]
[TD][/TD]
[TD]Column f[/TD]
[TD]Coulmn g[/TD]
[/TR]
[TR]
[TD]10[/TD]
[TD]Current Transition Point (calculated value)[/TD]
[TD]24/10/2017
[/TD]
[/TR]
[TR]
[TD]11
[/TD]
[TD]Last recorded transition point[/TD]
[TD]23/10/2017[/TD]
[/TR]
[TR]
[TD]12
[/TD]
[TD]Maximum Transition Point[/TD]
[TD]28/10/2017[/TD]
[/TR]
[TR]
[TD]13[/TD]
[TD]Minimum Transition point[/TD]
[TD]22/10/2017[/TD]
[/TR]
</tbody>[/TABLE]

 

[wideboydixon]

Right click the sheet name, select View Code and paste the following in to the VBA editor:

Private Sub Worksheet_Calculate()

If Me.Range("G10").Value > Me.Range("G12").Value Then Me.Range("G12").Value = Me.Range("G10").Value
If Me.Range("G10").Value < Me.Range("G13").Value Then Me.Range("G13").Value = Me.Range("G10").Value

End Sub

Since G10 is calculated (according to your description) then the above code should pick up any changes to the cell and copy the date to G12/13 as appropriate if necessary.

WBD

 

[gearhead29]

Hi WBD

That code works great for the min max values but doesn't address recording the value of the previous result, for direct day to day comparison. I was hoping so that when the document is closed/saved or even at opening it would update that value in g11 until saved/closed/opened again, is this possible?

 

[wideboydixon]

Ah. OK. You could perhaps do something when the workbook is opened to "remember" the value at the start of the session:

Private Sub Workbook_Open()

Range("G11").Value = Range("G10").Value

End Sub

Note that this needs to be pasted into the ThisWorkbook module in VB editor.

WBD