Prev: what is the formula for what I want to do
Next: Check column header value
From: Sacha David on 5 Mar 2010 03:01
Hi, I Have a cell say a1 that has a continuously changing number it
in and I want to track the highest number that it prints, and the
same for the lowest number in cell a2. I would like these results to
show up in the cells below them

Im very new to programming and your help would be much appreciated

many thanks

Sacha David
From: dk on 5 Mar 2010 03:25
On Mar 5, 1:01 pm, Sacha David <sach1...(a)googlemail.com> wrote:
> Hi,  I Have a cell say a1 that has a continuously changing number it
> in and I want to track the highest number that it prints,  and the
> same for the lowest number in cell a2. I would like these results to
> show up in the cells below them
>
> Im very new to programming and your help would be much appreciated
>
> many thanks
>
> Sacha David

One way is to write a macro:

Dim prvValue as integer

Sub Get_Max()

Application.Volatile

if prvValue<range("A1").value
prvValue = range("A1").value
end if
range("A2").value = prvValue

End Sub

From: Monarch on 5 Mar 2010 03:34
Right Click on worksheet label, click View Code and copy this code in
editor.
I assume that cell A2 is MAX number cell and cell B2 is MIN number cell
you can change their position
(Cells(2,1) = A2 and Cells(2,2) = B2 e.g. function is cells(row, column))

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address = "$A$1" Then
If Target.Value > Cells(2, 1).Value Then
Cells(2, 1).Value = Target.Value
ElseIf Target.Value < Cells(2, 2).Value Then
Cells(2, 2).Value = Target.Value
ElseIf Len(Cells(2, 2).Value) = 0 Then
Cells(2, 2).Value = Target.Value
End If
End If
End Sub

On 05.03.2010 09:01, Sacha David wrote:
> Hi, I Have a cell say a1 that has a continuously changing number it
> in and I want to track the highest number that it prints, and the
> same for the lowest number in cell a2. I would like these results to
> show up in the cells below them
>
> Im very new to programming and your help would be much appreciated
>
> many thanks
>
> Sacha David
From: Sach1810 on 5 Mar 2010 04:35
On 5 Mar, 08:34, Monarch <rpedr...(a)gmail.com> wrote:
> Right Click on worksheet label, click View Code and copy this code in
> editor.
> I assume that cell A2 is MAX number cell and cell B2 is MIN number cell
> you can change their position
> (Cells(2,1) = A2 and Cells(2,2) = B2 e.g. function is cells(row, column))
>
> Private Sub Worksheet_Change(ByVal Target As Range)
> If Target.Address = "$A$1" Then
>      If Target.Value > Cells(2, 1).Value Then
>          Cells(2, 1).Value = Target.Value
>      ElseIf Target.Value < Cells(2, 2).Value Then
>          Cells(2, 2).Value = Target.Value
>      ElseIf Len(Cells(2, 2).Value) = 0 Then
>      Cells(2, 2).Value = Target.Value
>      End If
> End If
> End Sub
>
> On 05.03.2010 09:01, Sacha David wrote:
>
>
>
> > Hi,  I Have a cell say a1 that has a continuously changing number it
> > in and I want to track the highest number that it prints,  and the
> > same for the lowest number in cell a2. I would like these results to
> > show up in the cells below them
>
> > Im very new to programming and your help would be much appreciated
>
> > many thanks
>
> > Sacha DavidT
Thanks for the answer but in the line

ElseIf Len(Cells(2, 2).Value) = 0 Then it expects end of statement
and will not let it go through anyideas

thanks again
From: Monarch on 5 Mar 2010 04:45
Please copy code from PRIVATE till END SUB
line with (cells(2,1) is sample as reference for you not part of code
you need only this, it is tested and work without problem

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address = "$A$1" Then
If Target.Value> Cells(2, 1).Value Then
Cells(2, 1).Value = Target.Value
ElseIf Target.Value< Cells(2, 2).Value Then
Cells(2, 2).Value = Target.Value
ElseIf Len(Cells(2, 2).Value) = 0 Then
Cells(2, 2).Value = Target.Value
End If
End If
End Sub

On 05.03.2010 10:35, Sach1810 wrote:
> On 5 Mar, 08:34, Monarch<rpedr...(a)gmail.com> wrote:
>> Right Click on worksheet label, click View Code and copy this code in
>> editor.
>> I assume that cell A2 is MAX number cell and cell B2 is MIN number cell
>> you can change their position
>> (Cells(2,1) = A2 and Cells(2,2) = B2 e.g. function is cells(row, column))
>>
>> Private Sub Worksheet_Change(ByVal Target As Range)
>> If Target.Address = "$A$1" Then
>> If Target.Value> Cells(2, 1).Value Then
>> Cells(2, 1).Value = Target.Value
>> ElseIf Target.Value< Cells(2, 2).Value Then
>> Cells(2, 2).Value = Target.Value
>> ElseIf Len(Cells(2, 2).Value) = 0 Then
>> Cells(2, 2).Value = Target.Value
>> End If
>> End If
>> End Sub
>>
>> On 05.03.2010 09:01, Sacha David wrote:
>>
>>
>>
>>> Hi, I Have a cell say a1 that has a continuously changing number it
>>> in and I want to track the highest number that it prints, and the
>>> same for the lowest number in cell a2. I would like these results to
>>> show up in the cells below them
>>
>>> Im very new to programming and your help would be much appreciated
>>
>>> many thanks
>>
>>> Sacha DavidT
> Thanks for the answer but in the line
>
> ElseIf Len(Cells(2, 2).Value) = 0 Then it expects end of statement
> and will not let it go through anyideas
>
> thanks again
 |  Next  |  Last
Pages: 1 2
Prev: what is the formula for what I want to do
Next: Check column header value