Updating a cell with the lowest historic value.

W

wguidry

New Member
Joined
Apr 22, 2003
Messages
42
I am trying to keep track of the lowest value ever seen by a particular cell. For instance. If cell "a1" is a calculated value that is dependent upon other cells, I would like to update a different cell "b1" with any value lower than the lowest low of cell "a1". I seem to be running in circles because I can't seem to store the current value of cell "a1" and then update it if the value changes to a lower number.

Here's what I have so far and it doesn't work.

Private Sub Worksheet_Change(ByVal a1 As Range)
Dim NewVal As Double
Static CurrVal
a1.Value = CurrVal
If CurrVal < CurrVal Then
CurrVal = Worksheets("sheet1").Cells("b1")
End If
End Sub

Thanks,
Will
 

Excel Facts

Which Excel functions can ignore hidden rows?
The SUBTOTAL and AGGREGATE functions ignore hidden rows. AGGREGATE can also exclude error cells and more.
Sort by date Sort by votes
This should do it

Private Sub Worksheet_Change(ByVal a1 As Range)
If Cells(2, 1) > Cells(1, 1) Then
Cells(2, 1) = Cells(1, 1)
End If
End Sub
 
Upvote 0
if cell A1 is a formula that is dependent on the value of other cells, then A1 will not be manually edited, but will change whenever the workbook is recalculated. therefore, the worksheet_change event would not work in this case. you need to use the calculate_event instead. something like this:

Private Sub Worksheet_Calculate()
If [A1].Value < [B1].Value Then [B1].Value = [A1].Value
End Sub
 
Upvote 0
Here is an alternative, non-VBA approach. It involves a circular reference that is easily overcome by doing this--

Under the Tools / Options / Calculation tab, make sure that Calculation is set to Automatic, and that the Iteration box is checked. Then enter the following formula in cell B1:

=IF(B1=0,A1,MIN(A1:B1))

When cells that A1 depends on are updated, A1 is updated, as is B1 (if appropriately the new minimum).

Kskinne's approach is probably superior, but this indicates that there are other (non-VBA) approaches that you might prefer in other situations . . .

--Tom
 
Upvote 0
Ohhhhh Yeah! That's really slick Tom. I was just about to call it quits after tying myself in knots trying to adapt Kevin's code to a range of 360 rows (I couldn't get the loop to work).

Anyway, I used your method and simply copied it down!

Great stuff.

Thank you both!
 
Upvote 0
instead of using this event:

Private Sub Worksheet_Calculate()
If [A1].Value < [B1].Value Then [B1].Value = [A1].Value
End Sub

you could use something like this:

Private Sub Worksheet_Calculate()
LastRow = Range("A65536").End(xlUp).Row
For x = 1 to LastRow
If Cells(x, 1).Value < Cells(x, 2).Value Then Cells(x, 2).Value = Cells(x, 1).Value
Next x
End Sub

to perform this on each used row, or if your range of data is fixed in size, for example 360, you would simply do away with the 'LastRow = ' line of code, and replace:

For x = 1 to LastRow

with:

For x = 1 to 360

hth
kevin
 
Upvote 0
Kevin, you ARE the Board Master! The code works great.

Tom's suggestion about turning off the circular reference notification worked great also, but I would rather be alerted to circ. ref. problems, so I'll use you code.

Thanks again.
 
Upvote 0
You must log in or register to reply here.

Similar threads

M
  • Question
Capturing Highest Historical Rank in a Continuously Updated Worksheet
2
Replies
11
Views
401
Mz309Lord
M
L
  • Solved
VBA to store previous value of continuously updating cell
Replies
3
Views
287
Joe4
Joe4
P
  • Solved
VBA to keep a running log of changes to specific cell
2 3
Replies
20
Views
873
Joe4
Joe4
C
  • Solved
Updating a specific cell value in an autofiltered table
Replies
3
Views
261
skillilea
S
A
  • Question
Sorting from the lowest to highest value
Replies
1
Views
94
dermie_72
D

Forum statistics

Threads
1,215,754
Messages
6,126,679
Members
449,328
Latest member
easperhe29

Share this page

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