Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Please help me solve my problem
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Please help me solve my problem
On Mar 5, 1:01*pm, 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 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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Please help me solve my problem
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Please help me solve my problem
On 5 Mar, 08:34, Monarch 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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Please help me solve my problem
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, 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 |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Please help me solve my problem
On 5 Mar, 08:25, dk wrote:
On Mar 5, 1:01*pm, 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 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 ok I have got this to work but it does not run continually, so it is constantly keeping track. Any ideas Thanks again Sacha |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
how can I solve this problem? Please help me | Excel Discussion (Misc queries) | |||
Is there any way to solve this problem ? | Excel Programming | |||
CAN ANYONE PLEASE SOLVE MY PROBLEM | Excel Programming | |||
Pls help me to solve this problem... | Excel Worksheet Functions | |||
Problem when multipple users access shared xl-file at the same time, macrocode for solve this problem? | Excel Programming |