Need urgent help in excel please

Yongle

Well-known Member
Joined
Mar 11, 2015
Messages
5,260
Office Version
365
Platform
Windows
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
VBA Code:
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
 

Some videos you may like

Excel Facts

Enter current date or time
Ctrl+: enters current time. Ctrl+; enters current date. Use Ctrl+: Ctrl+; Enter for current date & time.

Yongle

Well-known Member
Joined
Mar 11, 2015
Messages
5,260
Office Version
365
Platform
Windows
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
VBA Code:
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

New Member
Joined
Mar 16, 2020
Messages
29
Office Version
2016
Platform
Windows
No, I want only this, but cell Z1 do not show the minimum value its only show the current value of cell A1
 

Yongle

Well-known Member
Joined
Mar 11, 2015
Messages
5,260
Office Version
365
Platform
Windows
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
:unsure: :unsure:

Please explain exactly what you want

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

Yongle

Well-known Member
Joined
Mar 11, 2015
Messages
5,260
Office Version
365
Platform
Windows
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


VBA Code:
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

Well-known Member
Joined
Mar 11, 2015
Messages
5,260
Office Version
365
Platform
Windows
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

New Member
Joined
Mar 16, 2020
Messages
29
Office Version
2016
Platform
Windows
Yes, Code of post#26 is works.
I want at every time the workbook is opened
 

Yongle

Well-known Member
Joined
Mar 11, 2015
Messages
5,260
Office Version
365
Platform
Windows
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
VBA Code:
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

New Member
Joined
Mar 16, 2020
Messages
29
Office Version
2016
Platform
Windows
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?
 

Forum statistics

Threads
1,089,531
Messages
5,408,821
Members
403,231
Latest member
FlorianPhil

This Week's Hot Topics

  • help please
    SORRY NOT ANY GOOD AT EXCEL SO HELP WOULD BE MUCH APPRECIATED this formula is in a sheet called ignore...
  • two formulas needed
    Hello, I'll try my best to explain this: First formula needed in Sheet1 cell A2: If Sheet1 cell B2 = Sheet2 cell B2 then return a 1. If not then...
  • Dynamic Counts
    Good afternoon, we are tidying up some data & the data seems to be growing quicker than we are tidying it up! What we confirm (by reviewing it...
  • Help Excel formula eliminate duplicate values and keep only 2 identical rows.
    as picture below column A has a duplicate value. but the values are not the same as the rule. sometimes 4 rows, sometimes 10 rows or 7 or 9...
  • Macro Compile Error Sub or Function not defined
    Hello, I am trying to run macros from a validation list, all macros have been created and run perfectly on there own but I'm getting a compile...
  • Last row combined with Current Region VBA
    I'm generally happy finding the last row of data through something like Lastrow = Cells(Rows.Count, "D").End(xlUp) but I don't always receive data...
Top