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

i think i have found the answer here http://www.vbaexpress.com/kb/getarticle.php?kb_id=530 but if there is a more simple way i'm open to answers

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

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

"Private Sub Worksheet_Calculate()
    Dim target As Range
    Set target = Range("D4")


    If Not Intersect(target, Range("d4")) Is Nothing Then
  ' option to  Call MyCode if required
    Range("a5") = "Match"
    End If
End Sub
Sub MyCode()
'more code here if required
End Sub
"

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