Need urgent help in excel please

[danish6061]

In a cell that is continuously changing every second, i need to monitor that cell and detect its highest & lowest values during a day, how can i achieve this, pls help

 

[Yongle]

First you need to make it work in a very simple way

1. Create a new workbook

2. In cell B1 enter this formula
=A1

3. Right-click on sheet tab \ view code \ insert code below

Private Sub Worksheet_Calculate()
    Debug.Print "Triggered " & Time
    Call MacroY(Me)
End Sub

Sub MacroY(sh As Worksheet)
        sh.Range("C1") = WorksheetFunction.Max(sh.Range("A1"), sh.Range("C1"))
End Sub

4. now see what happens when value in A1 is amended
Enter 50 in A1 - Look at B1 & C1
Enter 90 in A1 - Look at B1 & C1
Enter 20 in A1 - Look at B1 & C1

C1 retains the maximum value

5. Look at the immediate window in VBA
(make immediate window visible in VBA by clicking on View \ Immediate Window)
- there you will see a list of times triggered entries
- you will need to use this technique later when sorting out other workbook!


After this is working for you, then we will work on your other workbook

 

[danish6061]

Yes Yongle, Its working. What's next?

 

[Yongle]

Now you will do EXACTLY the same thing in the original file

I assume that cells Z1, Z2 and Z3 are not being used
(Z3 will be used in the next step)

In the original file ...

1. delete all previous code - both module code and sheet code

2. In cell Z1 in the relevant sheet enter this formula
=A1

3. Right-click on sheet tab \ view code \ insert code below

Private Sub Worksheet_Calculate()
    Debug.Print "Triggered " & Time
    Call MacroY(Me)
End Sub
      
Sub MacroY(sh As Worksheet)
    sh.Range("Z2") = WorksheetFunction.Max(sh.Range("A1"), sh.Range("Z2"))
End Sub

4. Test by manually amending the value in A1 twice - the value in Z1 and Z2 will change

5. Ensure that cell A1 is being updated by the internet
- Z1 should change when A1 changes
- Z2 should change when new value in A1> current value in Z2

You can also confirm that internet updates are triggering the code by checking the immediate window which will look like this
Triggered 11:58:18
Triggered 11:59:19
Triggered 12:00:19

6. When this is working correctly - let me know
7. Do you want to use cells Z1, Z2 Z3 or different cells? - let me know which cells

 

[danish6061]

No, I want only this, but cell Z1 do not show the minimum value its only show the current value of cell A1

 

[Yongle]

You said this in post#1

In a cell that is continuously changing every second, i need to monitor that cell and detect its highest & lowest values during a day, how can i achieve this, pls help

You said this in post#24

No, I want only this, but cell Z1 do not show the minimum value its only show the current value of cell A1

Please explain exactly what you want

I know that you also want the minimum value - that will be in Z3 (next step)

 

[Yongle]

This is the code that takes you to the next step
(and we are still not finished - I am taking you through this step by step)

- remove the previous code
- replace with code below

Private oldValue As Variant

Private Sub Worksheet_Calculate()
'the next line prevents unnecessary triggers
    If Range("A1") = oldValue Then Exit Sub
    Debug.Print "Triggered " & Time
    oldValue = Range("A1").Value
    Call MacroY(Me)
End Sub

Sub MacroY(sh As Worksheet)
    sh.Range("Z2") = WorksheetFunction.Max(sh.Range("A1"), sh.Range("Z2"))
    If sh.Range("Z3") = 0 Then sh.Range("Z3") = 99999999
    sh.Range("Z3") = WorksheetFunction.Min(sh.Range("A1"), sh.Range("Z3"))    
End Sub

Test again to see if Z2 and Z3 provide the max and min values

 

[Yongle]

If the code in post#26 works for you, then the next step is to make sure the values are reset correctly

At the moment the values are not being reset
When should the values be reset ?
- EVERY time the workbook is opened
- the FIRST time the workbook is opened every day

 

[danish6061]

Yes, Code of post#26 is works.
I want at every time the workbook is opened

 

[Yongle]

I want at every time the workbook is opened

Variable oldValue is automatically reset to nothing when the workbook opens
- the code uses oldValue as a test, allowing Z2 and Z3 to be reset

All code is in the ONE sheet code module
- sheet references all removed in this version
( VBA knows that Range("A1") is A1 in the sheet containing the code )
- it keeps things simple
- simple is good!

Delete all previous code. Replace with code below

Private oldValue As Variant

Private Sub Worksheet_Calculate()
    If CStr(oldValue) = "" Then
        Range("Z3") = 9999999
        Range("Z2") = 0
    End If
    If Range("A1") = oldValue Then Exit Sub
    oldValue = Range("A1").Value
    Call MacroY
End Sub

Sub MacroY()
    Range("Z2") = WorksheetFunction.Max(Range("A1"),Range("Z2"))
    If Range("Z3") = 0 Then Range("Z3") = 9999999
    Range("Z3") = WorksheetFunction.Min(Range("A1"), range("Z3"))
End Sub

 

[danish6061]

Thanks Yongle,
I am using both codes of post#26 and post#29 in different workbook,
Now I want to do the same thing with cell a18 where the data will be change and Max. and Min. value should be shown in k18 and k19 in both workbook.
Could you provide me a code please?