Posts by kumarsatish

    To increase the speed, I took nearly whole day in designing an alternative, if possible.


    Currently, with the code of pike, when cell AA1 is changed manually to trigger the code, the hourglass appears for 2.5 seconds approximately before the final results are populated in the desired cells.


    I re-designed the sheet & a new code only for trial / beta.


    In this I am using variables

    • data range (first/last column)
    • criteria as “Y”
    • headers (row) in row #6


    The code works very fast, the write took about 1/4 seconds but I don’t know how to set the trigger in the code i.e. the code should trigger only when L4=111.
    Can someone modify this code to make it trigger when L4=111?

    Files

    • CopyPaste.txt

      (936 Byte, downloaded 62 times, last: )
    • Get Values.xlsm

      (22.88 kB, downloaded 61 times, last: )

    I added the above 2 lines


    Thereafter I performed following 4 actions on the Worksheet & results were confusing.


    First action: In AA1 inserted 10/18/2019 3:31:30 PM manually. Z2=10/19/2019 3:31:30 PM. L4 became 111. The code performed successfully.


    Second action: Deleted output arrays; In AA1 inserted 10/20/2019 3:31:30 PM L4 became 222 & the output arrays were not copy/pasted. The code performed successfully.


    Third action: Again In AA1 inserted 10/18/2019 3:31:30 PM manually. But L4 remained 222 instead of becoming 111 & thereby nothing got copy/pasted in the output arrays.


    Fourth action: Clicked Save button & then again inserted 10/18/2019 3:31:30 PM manually in AA1. L4 became 111 & the code performed successfully.


    Comments please

    The code works & is performing the ‘desired actions’.


    But I noticed that the calculation speed has slowed down. I am at a scratch level with minimal coding knowledge and that’s why would request you to speed up the code, if it can be done.


    If allowed to be spoken, you may also change the design of this Worksheet to speed up the code meeting the ‘desired actions’.


    I use results of the above desired actions performed by the code 3 to 4 times in a day but cannot afford to do the copy/paste special manually since the values in B2:B10 changes at a faster rate.
    There are other Worksheets also with formulas (in this Workbook).

    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

    Files

    • Code_H019.txt

      (847 Byte, downloaded 63 times, last: )

    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.

    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 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 (""“”).

    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.

    Files

    • Result.xlsx

      (8.8 kB, downloaded 58 times, last: )

    Does this mean that you want it to trigger after you enter 111 in L4?

    L4 has formula & either of 111 or 222 gets generated ‘periodically’ during a day. Yes, the code should get triggered if L4=111.

    If you enter 112, then nothing happens. Is this your intent?

    Yes, nothing should happen if L4<>111

    Or if you want to trigger it manually from the main sheet, click on Alt F8 and select the macro to run.

    No, I want to trigger code automatically not manually.

    Worksheet’s range D7:D133; N7:N133 & O7:O133 gets updated on RTD basis due to feeds in another linked cells of another Worksheet in the SAME Workbook.

    I need vba to ‘grab’ the values present 'currently' from the above range in output range L7:L133; S7:S133 & T7:T133 respectively if L4=111

    I am completely clueless how to even start writing a macro for this.


    Appreciate your help

    Files

    IT WORKS….Your Code Works……..I am so happy that ……..unable to express now…….that little modification in the A2=”z” has to be done because……….I’ll tell you tomorrow…….Currently overwhelmed because of you…..EXICITED…….HAPPY…..Thank you CARIM :wowee::wowee::wowee: