using formula or similar to paste maximum value in cell in excel 2

[torwalker]

Hi I want to use a formula or similar to update a cell when another cell has
a higher value, and maintain this relationship, e.g. cell B1 will always
contain the maximum value held by cell A1 over time where A1 is a calculated
value. So I want to paste the value from A1 into B1 when A1 exceeds B1 but
not otherwise and as there is a whole column I would like this to be
automatic.
Sorry but I can't for the life of me work out how to do it and haven't found
a forum topic that does what I want. Any help greatly appreciated.
Regards

 

[JLatham]

I'd use the following code attached to the worksheet:

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address <> "$A$1" Then
Exit Sub
End If
If Target.Value > Range("B1").Value Then
Range("B1") = Target
End If
End Sub

To put the code in the proper place, right-click on the worksheet's name
tab and choose [View Code] from the popup list. Copy and paste the code
above into the code module presented to you. Close the VB Editor and save
the workbook.

Read this:

http://www.mcgimpsey.com/excel/accumulator.html

This is mostly about SUM(), but the principles remain the same for a maximum

 

[Mike H]

Hi,

This assumes column A changes because of a calculation on the same sheet,
you could use the worksheet change event. Right click your sheet tab, view
code and paste the code below in

Private Sub Worksheet_Calculate()
lastrow = Cells(Cells.Rows.Count, "A").End(xlUp).Row
Set MyRange = Range("A1:A" & lastrow)
For Each c In MyRange
If c.Value > c.Offset(, 1).Value Then
c.Offset(, 1).Value = c.Value
End If
Next
End Sub

or

Private Sub Worksheet_Change(ByVal Target As Range)

Mike

 

[torwalker]

Just wanted to say thanks for replies. I should have realised a bit of code
was required. Working nicely now!
Thanks again all.

 

[Chip Pearson]

You can use a circular reference to do this. On the Tools menu, choose
Options, then the Calculation tab. There, check the "Iterations" box.
Then, use the following formula

=MAX(A1,B1)

B1 will be a perisistent maximum of A1. If A1 exceeds B1, B1 will get
this new value. If A1 is less than B1, B1 remains the same. In other
words, B1 will be the maximum value ever reached by A1, regardless of
the current value of A1.

Cordially,
Chip Pearson
Microsoft Most Valuable Professional
Excel Product Group, 1998 - 2009
Pearson Software Consulting, LLC
www.cpearson.com
(email on web site)