Grab values from a range to different ranges meeting desired goals & then clear it

Important Notice


Please note that on 14th December 2023 users will experience an expected outage whilst we make upgrades to our network. We anticipate this process may take a couple of hours and so we apologise in advance for any inconvenience.

    My Worksheet range B2:B10 are dynamic cells & keeps on getting updated continuously on real time basis as long as the Workbook remains opened.


    Cell A1 which contains formula generates 1 or 0 & just dictates whether the values in Column E (E2:E10); Column F (F2:F10); Column G (G2:G10); Column H (H2:H10) & Column I (I2:I10) should continue to update or be frozen to whatever values they currently are with the rule that If A1=1, columns E; F; G; H & I should continue to update & if A1=0, columns E; F; G; H & I should stop updating and be frozen to whatever values they are.

    I need vba to execute the following actions:

    • Column E (E2:E10) should ‘grab’ MAXIMUM value generated in B2:B10 when A1=1
    • Column F (F2:F10) should ‘grab’ MINIMUM value generated in B2:B10 when A1=1
    • Column G (G2:G10) should get the FIRST OCCURRENCE value generated in B2:B10 when A1=1
    • Column H (H2:H10) should get the LARGEST NUMBER THAT IS SMALLER THAN THE MAXIMUM NUMBER generated in B2:B10 when A1=1
    • Column I (I2:I10) should get the SMALLEST NUMBER THAT IS HIGHER THAN THE MINIMUM NUMBER generated in B2:B10 when A1=1
    • Reset columns E; F; G; H & I to null (“”) when A2=444 (A2 contains formula & generates either of 333 or 444)


    Code should execute the above actions speedily.

    Any help is greatly appreciated.

    I appreciate for the time being given for my Workbook
    So if E2 is the maximum of B2:B10 what goes in E3?


    In E3=MAXIMUM of the different values generated in the cell B3.
    In E2=MAXIMUM of the different values generated in B2 & so on.


    How is A1 being updated?


    A1 contains formula (based on time) & generates either of 1 or 0.


    Will A1 go back to 0 and then back to 1 and you put the next maximum in E3?


    When A1=1, the first 5 actions needs to be performed by the code
    When A1=0, code should not ‘look’ further in B2:B10 for the maximum, minimum, first occurrence, 2nd largest & 2nd smallest. The values ‘grabbed’ in columns E; F; G; H & I (E2:E10; F2:F10; G2:G10; H2:H10; I2:I10) should remain in their respective cells as populated. Here I would like to add that these values should remain in their respective cells even if the Excel Workbook gets closed due to some technical glitch like power failure, if it can be done.


    A1=1 for a certain time period which is continuous (Example A1=1 from 18/10/2019 10:30:00 AM to 18/10/2019 11:00:00 AM)
    It does not keeps on generating 1 or 0 at irregular intervals i.e. in above example A1=1 from 18/10/2019 10:30:00 AM to 18/10/2019 11:00:00 AM & it will not become 0 in-between. So the values which are generated at a fast speed in B2:B10 can be ‘captured’ by the code using the 5 criteria’s & placed in columns E; F; G; H & I (E2:E10; F2:F10; G2:G10; H2:H10; I2:I10)


    When A2=444, all values from columns E; F; G; H & I should become null (""“”).

    OK that has shed some light.


    Are you able to add these formula to the workbook or the thread here? I ask because I wonder if the values are coming from an external system as opposed to being generated within Excel.

    Are you able to add these formula to the workbook or the thread here?


    Sure, please see the screenshot.
    H019
    [TABLE="border: 1, cellpadding: 0, cellspacing: 0"]

    [tr]


    [td][/td]


    [td]

    B

    [/td]


    [/tr]


    [tr]


    [td]

    2

    [/td]


    [td]

    278.25

    [/td]


    [/tr]


    [tr]


    [td]

    3

    [/td]


    [td]

    580.35

    [/td]


    [/tr]


    [tr]


    [td]

    4

    [/td]


    [td]

    640.10

    [/td]


    [/tr]


    [tr]


    [td]

    5

    [/td]


    [td]

    10000.95

    [/td]


    [/tr]


    [tr]


    [td]

    6

    [/td]


    [td]

    200.25

    [/td]


    [/tr]


    [tr]


    [td]

    7

    [/td]


    [td]

    600.00

    [/td]


    [/tr]


    [tr]


    [td]

    8

    [/td]


    [td]

    500.00

    [/td]


    [/tr]


    [tr]


    [td]

    9

    [/td]


    [td]

    200.00

    [/td]


    [/tr]


    [tr]


    [td]

    10

    [/td]


    [td]

    35.65

    [/td]


    [/tr]


    [/TABLE]

    Spreadsheet Formulas
    CellFormula
    B2='7'!I2
    B3='7'!I3
    B4='7'!I4
    B5='7'!I5
    B6='7'!I6
    B7='7'!I7
    B8='7'!I8
    B9='7'!I9
    B10='7'!I10
    [TABLE="border: 1, cellpadding: 2, cellspacing: 0"]
    [tr][td]


    [/td]


    [/tr]


    [/TABLE]

    I ask because I wonder if the values are coming from an external system as opposed to being generated within Excel.


    Let me first clarify that my Workbook is being used for stock market data analysis.
    B2:B10 is the ‘Last Traded Price’ of 9 scrips (chosen in the market-watch of NEST) which keeps on updating when the stock market’s trading software (which is called NEST) is kept ‘on’ & the data on the market-watch is linked to the excel Worksheet, kept ‘open’, where the data in the excel Worksheet (tab named ‘7’) will keep getting updated or refreshed, as the broadcast keeps getting refreshed in the market-watch of NEST.

    The ‘Last Traded Price’ is updated or refreshed (in Worksheet ‘7’) at a high speed & the values in B2:B10 (in Worksheet ‘H019’) is immediately updated.

    OK I'm not sure how your system will work so perhaps we can do a little test. This is not really answering your questions, but if it works I know the approach is broadly correct and if it doesn't work it will need a re-think.


    Can you paste this code into the sheet module (right-click the sheet tab, View Code and paste):

    Code
    Private Sub Worksheet_Calculate()


If Range("A1").Value = 1 Then
    Range("E2").Value = WorksheetFunction.Max(Range("B2:B10"))
End If


End Sub


    When the sheet calculates, if A1=1 it should just put the max of B2:B10 in E2.


    Let me know how you get on.


    ______________________


    EDIT: if that doesn't work, can you try this code in the module of sheet "7" and see if it runs whenever I2 is changed.

    I am going to try your code & definitely give the feedback.

    Meanwhile, let me say I am currently using below code which performs action #1 & action #2. But it has 3 flaws:

    • I am unable to code for action #3, 4, 5 & 6
    • Code is slow.
    • Values ‘grabbed’ in columns E; F; G; H & I (E2:E10; F2:F10; G2:G10; H2:H10; I2:I10) should remain in their respective cells as populated even if the Excel Workbook gets closed due to some technical glitch like power failure; but it is not so

    Because of above 3 weakness / deficiencies in my code, I had approached this forum for help

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!

Register Yourself Login