I have a spreadsheet in which a value is automatically imported into B2
each day.

I would like B3 to automatically show the maximum value ever reached in
B2 and, in B4, the minimum ever reached by B2. Could someone advise me
on how I might do this please.

TIA.

--
F
(Beware of spam trap - remove the negative)

Put the following code in the Worksheet module:

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Cells.Count = 1 Then
If Target = Cells(2, 2) Then
If Cells(2, 2) Cells(3, 2) Then
Cells(3, 2) = Cells(2, 2)
End If
If Cells(2, 2) < Cells(4, 2) Then
Cells(4, 2) = Cells(2, 2)
End If
End If
End If
End Sub

RBS

"F" wrote in message
...
I have a spreadsheet in which a value is automatically imported into B2
each day.

I would like B3 to automatically show the maximum value ever reached in B2
and, in B4, the minimum ever reached by B2. Could someone advise me on how
I might do this please.

TIA.

--
F
(Beware of spam trap - remove the negative)

On 01/09/2006 20:18 RB Smissaert wrote:

Put the following code in the Worksheet module:

Thanks for the help: appreciated. However, I'm not sure what you mean by
the above.

--
Frank
(Beware of spam trap - remove the negative)

Copy the posted code
Have your workbook open
Press Alt + F11
Press Ctrl + R
In the left pane right-click the sheet module that belongs
to the sheet on which you want to run the code.
Do View code.
Paste the code in the right hand pane
Back to the sheet with Alt + F11
Try it out

RBS


"F" wrote in message
...
On 01/09/2006 20:18 RB Smissaert wrote:

Put the following code in the Worksheet module:

Thanks for the help: appreciated. However, I'm not sure what you mean by
the above.

--
Frank
(Beware of spam trap - remove the negative)

On 02/09/2006 12:53 RB Smissaert wrote:

Copy the posted code

[snip]

Try it out

Many thanks - it does exactly what I wanted!

It's led me to adapt it and use it in another sheet. This has a value in
B2 updated each day and I've been able to store the maximum ever reached
in J2 and the minimum ever reached in K2.

However, I can't see how to persuade it to work on different values
contained in a range of cells within a column, ie look at B2 and update
J2 or K2, then look at B3 and update J3 or K3 and so on down to, say,
B27 and update J27 or K27.

Further help would be extremely welcome.

--
Frank
(Beware of spam trap - remove the negative)

Just repeat this bit, but with different values to account
for the different cells:

If Target = Cells(2, 2) Then
If Cells(2, 2) Cells(3, 2) Then
Cells(3, 2) = Cells(2, 2)
End If
If Cells(2, 2) < Cells(4, 2) Then
Cells(4, 2) = Cells(2, 2)
End If
End If

So, you will get 20 or so of these chunks of code.
You could make it a bit better looking and faster to code by running a loop,
something like:

Dim i as long

For i = 2 to 27
If Target = Cells(i, 2) Then
If Cells(i, 2) Cells(i + 1, 2) Then
Cells(i + 1, 2) = Cells(i, 2)
End If
If Cells(i, 2) < Cells(i + 2, 2) Then
Cells(i + 2, 2) = Cells(i, 2)
End If
End If
Next i


RBS


"F" wrote in message
...
On 02/09/2006 12:53 RB Smissaert wrote:

Copy the posted code

[snip]

Try it out

Many thanks - it does exactly what I wanted!

It's led me to adapt it and use it in another sheet. This has a value in
B2 updated each day and I've been able to store the maximum ever reached
in J2 and the minimum ever reached in K2.

However, I can't see how to persuade it to work on different values
contained in a range of cells within a column, ie look at B2 and update J2
or K2, then look at B3 and update J3 or K3 and so on down to, say, B27 and
update J27 or K27.

Further help would be extremely welcome.

--
Frank
(Beware of spam trap - remove the negative)

On 03/09/2006 19:19 RB Smissaert wrote:

You could make it a bit better looking and faster to code by running a
loop

I used the loop.

Again, many thanks for your help.

--
Frank
(Beware of spam trap - remove the negative)

On 03/09/2006 20:02 F wrote:

I used the loop.

It was working perfectly if I entered the values manually but if I cut
values from another sheet and paste them into the one with the code the
maximum and minimum cells don't update.

The maximum and minimum cells also fail to update if the data is
automatically read into the sheet in question from another sheet which
is updated with values being entered manually or through cut and paste.

Any idea what I'm doing wrong?

TIA.

--
Frank
(Beware of spam trap - remove the negative)

Try commenting out this bit:
If Target.Cells.Count = 1 Then
With the corresponding End If.

RBS

"F" wrote in message
...
On 03/09/2006 20:02 F wrote:

I used the loop.

It was working perfectly if I entered the values manually but if I cut
values from another sheet and paste them into the one with the code the
maximum and minimum cells don't update.

The maximum and minimum cells also fail to update if the data is
automatically read into the sheet in question from another sheet which is
updated with values being entered manually or through cut and paste.

Any idea what I'm doing wrong?

TIA.

--
Frank
(Beware of spam trap - remove the negative)

On 05/09/2006 00:01 RB Smissaert wrote:

Try commenting out this bit:
If Target.Cells.Count = 1 Then
With the corresponding End If.

Thanks for the suggestion but the maximum and minimum cells still don't
update.

--
Frank
(Beware of spam trap - remove the negative)

Try leaving out:
If Target.Cells.Count = 1 Then (+ the corresponding End If)
and replace it with:
On Error Resume Next

RBS

"F" wrote in message
...
On 05/09/2006 00:01 RB Smissaert wrote:

Try commenting out this bit:
If Target.Cells.Count = 1 Then
With the corresponding End If.

Thanks for the suggestion but the maximum and minimum cells still don't
update.

--
Frank
(Beware of spam trap - remove the negative)

On 06/09/2006 23:36 RB Smissaert wrote:

Try leaving out:
If Target.Cells.Count = 1 Then (+ the corresponding End If)
and replace it with:
On Error Resume Next

Thanks for your patience but the changes are still not being picked up.

--
Frank
(Beware of spam trap - remove the negative)

It works with me.
What is your exact code and what exactly are you doing?

RBS

"F" wrote in message
...
On 06/09/2006 23:36 RB Smissaert wrote:

Try leaving out:
If Target.Cells.Count = 1 Then (+ the corresponding End If)
and replace it with:
On Error Resume Next

Thanks for your patience but the changes are still not being picked up.

--
Frank
(Beware of spam trap - remove the negative)

On 07/09/2006 00:04 RB Smissaert wrote:

It works with me.
What is your exact code and what exactly are you doing?

The spreadsheet is to record share prices and has a long history. It
originally took share prices from Teletext but now they come from an
Excel spreadsheet which I downloaded from Yahoo Finance.

I copy the values from that sheet and paste them into a sheet
("Imported") in the spreadsheet I'm trying to alter. Another sheet in
that workbook ("Fractions lookup") reads the values from "Imported" and
then a third sheet ("Valuation") reads from "Fractions lookup". It's
within this third sheet that I want to have the maximum and minimum
values recorded.

I realise this sounds very convoluted but it's as a result of the long
history and different sources of share price data.

To try to make what I've described clearer, I've uploaded a very much
simplified copy of the spreadsheet (which contains a macro to move some
values around) to
http://www.spinningweb.force9.co.uk/...MaxMinTest.xls

Thanks again for your help.

--
Frank
(Beware of spam trap - remove the negative)

I had a look at the WB, but I don't understand it.
There is one Sub that moves some data, but what exactly is this Sub supposed
to do?
Mainly, from what sheet does it copy and to where?
I can't make that out from that Sub:

Sub MoveValues()

Range("C32:J32").Select
Application.CutCopyMode = False
Selection.Cut

Range("B32").Select
ActiveSheet.Paste

Range("D30").Select
Selection.Copy

Range("J32").Select
Selection.PasteSpecial Paste:=xlValues, _
Operation:=xlNone, _
SkipBlanks:=False, _
Transpose:=False

End Sub


RBS


"F" wrote in message
...
On 07/09/2006 00:04 RB Smissaert wrote:

It works with me.
What is your exact code and what exactly are you doing?

The spreadsheet is to record share prices and has a long history. It
originally took share prices from Teletext but now they come from an Excel
spreadsheet which I downloaded from Yahoo Finance.

I copy the values from that sheet and paste them into a sheet ("Imported")
in the spreadsheet I'm trying to alter. Another sheet in that workbook
("Fractions lookup") reads the values from "Imported" and then a third
sheet ("Valuation") reads from "Fractions lookup". It's within this third
sheet that I want to have the maximum and minimum values recorded.

I realise this sounds very convoluted but it's as a result of the long
history and different sources of share price data.

To try to make what I've described clearer, I've uploaded a very much
simplified copy of the spreadsheet (which contains a macro to move some
values around) to
http://www.spinningweb.force9.co.uk/...MaxMinTest.xls

Thanks again for your help.

--
Frank
(Beware of spam trap - remove the negative)

On 09/09/2006 00:08 RB Smissaert wrote:

I had a look at the WB, but I don't understand it.
There is one Sub that moves some data, but what exactly is this Sub
supposed to do?
Mainly, from what sheet does it copy and to where?

It's assigned to a button on the Valuation sheet. When I click on it it
moves the values in a series of cells on that sheet one cell to the left
and then inserts the value from another cell in the now vacated cell. If
that makes sense!

In the cut down spreadsheet I uploaded it has no use at all as the cells
it is aimed at are empty. I just left it in in case it was stopping your
code.

If you paste (or manually enter) a couple of values into E1 and E2 on
the Imported sheet the relevant cells, A1 and A2 on the Fractions lookup
sheet, are updated which in turn update E2 and E3 on the Valuation
sheet. However, the maximum and minimum values in I2, I3, J2 and J3 on
the Valuation sheet don't change. If the contents of E2 and E3 on the
Valuation sheet are changed manually, then the maximum and minimum cells
update correctly.

I fail to understand why. Weird.

--
Frank
(Beware of spam trap - remove the negative)

I think this is simple.
The Worksheet_Change event won't be triggered if the result of a formula
changes.
You could say the formula stays the same, so the worksheet doesn't change.

You will have to put your code in the worksheet where the actual values
change and
let the code in that Worksheet_Change event change your sheet Valuation.

Having said that maybe there is some workaround and you could do a NG search
in Google
with these keywords: Worksheet_Change formula result

The other option is to change a cell value in the sheet Valuation, say A5
with every update and
alter your Worksheet_Change event code in that sheet like this:

Private Sub Worksheet_Change(ByVal Target As Range)

Dim i As Long

If Target = Cells(5, 1) Then
For i = 2 To 3
If Cells(i, 5) Cells(i, 10) Then
Cells(i, 10) = Cells(i, 5)
End If
If Cells(i, 5) < Cells(i, 9) Then
Cells(i, 9) = Cells(i, 5)
End If
Next i
End If

End Sub

You could make A5 a change counter for example, so every time there is a
change you will do:
Cells(5,1) = Cells(5,1) + 1


RBS


"F" wrote in message
...
On 09/09/2006 00:08 RB Smissaert wrote:

I had a look at the WB, but I don't understand it.
There is one Sub that moves some data, but what exactly is this Sub
supposed to do?
Mainly, from what sheet does it copy and to where?

It's assigned to a button on the Valuation sheet. When I click on it it
moves the values in a series of cells on that sheet one cell to the left
and then inserts the value from another cell in the now vacated cell. If
that makes sense!

In the cut down spreadsheet I uploaded it has no use at all as the cells
it is aimed at are empty. I just left it in in case it was stopping your
code.

If you paste (or manually enter) a couple of values into E1 and E2 on the
Imported sheet the relevant cells, A1 and A2 on the Fractions lookup
sheet, are updated which in turn update E2 and E3 on the Valuation sheet.
However, the maximum and minimum values in I2, I3, J2 and J3 on the
Valuation sheet don't change. If the contents of E2 and E3 on the
Valuation sheet are changed manually, then the maximum and minimum cells
update correctly.

I fail to understand why. Weird.

--
Frank
(Beware of spam trap - remove the negative)

On 09/09/2006 08:47 RB Smissaert wrote:

I think this is simple. The Worksheet_Change event won't be triggered
if the result of a formula changes. You could say the formula stays
the same, so the worksheet doesn't change.

That appears to be the case.

You will have to put your code in the worksheet where the actual
values change and let the code in that Worksheet_Change event change
your sheet Valuation.

Did that and used the On error line. It works fine. Many thanks for your
patience!

--
Frank
(Beware of spam trap - remove the negative)