worksheet code to copy the value of a formula result that constantly changes

  • Hi gurus


    i'm stumped on this and i'll try to explain what i'm trying to achieve, but have uploaded a simplified version as well if my explanation is poor.


    I capture external data in to Range A1:A50 of numbers , this data changes every minute or so, but sometimes a lot quicker


    In Range B1:B50 is a formula crunching numbers in Range A1:A50.


    In range C1:C50 is a formula to get a match result if reaching a criteria e.g If B6 value is >5 and < 10 "match"


    The problem is as the values in column A6 changes the word "match" comes and goes in column C6


    So in column D6 i want to paste the value "match" if it appears in column B so it stays in place in D6 whether it stays in C6 or not.


    I gather it will be a trigger event in worksheet code rather than a macro, but from here i'm stuck.


    Appreciate any help


    Thanks in advance


    dunc

  • Re: worksheet code to copy the value of a formula result that constantly changes


    just when i thought i'd found the answer. i failed miserably to get the code to work :(

  • Re: worksheet code to copy the value of a formula result that constantly changes


    That code would not work because a cell changing as a result of a calculation does not raise the Change event. You could try the code in the Calculate Event, scanning the relevant cells for 'Match', or you could use a Circular reference in E4


    =IF(AND(D4="match", E4=0), D4, E4)


    if D4 = "Match" and E4 is 0 (it will be 0 iwhen you first edit/copy the formula) then take the value of D4, otherwise take the value of E4. You will have to Enable Iterative Calculations and set the Max iterations to 1 in the Options/Formulas screen.

  • Re: worksheet code to copy the value of a formula result that constantly changes


    Quote from Grimes0332;768874

    That code would not work because a cell changing as a result of a calculation does not raise the Change event. You could try the code in the Calculate Event, scanning the relevant cells for 'Match', or you could use a Circular reference in E4


    =IF(AND(D4="match", E4=0), D4, E4)


    if D4 = "Match" and E4 is 0 (it will be 0 iwhen you first edit/copy the formula) then take the value of D4, otherwise take the value of E4. You will have to Enable Iterative Calculations and set the Max iterations to 1 in the Options/Formulas screen.


    many thanks for that, i'll give it a try but the "Enable Iterative Calculations and set the Max iterations to 1 in the Options/Formulas screen" is beyond my knowledge, but i'm sure searching this forum and google will answer that.
    Thanks again for looking and providing your assistance :)

  • Re: worksheet code to copy the value of a formula result that constantly changes


    hi again
    i couldn't get the match to work but i think i have managed to get nearly a desired result with



    Although it picks up with the change of cell i think i can make it work for me, thanks though for your input, but i'm off to bed and will try and put this into practice tomorrow