QUESTION: Hi Aidan The subject may be a bit misleading as there may be several ways of approaching this problem though it does point to what i want to be achieved. I have anworkbook with an external dde link that updates data contained in tables in sheet 1 every 15 seconds or so. In sheet two i have several formulas dependent on the data in these tables. therefore the values produced by these formulas update along with the data in sheet 1. What i would like is if certain conditions are met that the value produced by a formula is copy pasted (auto) to another cell and in future updates where the condition is met again the new value is copy pasted to the same cell to replace the old value. Could i conditional format and the use the format as a trigger to initiate the copy paste and if so how. Could calculate event be used. Would greatly appreciate any help you could provide.
ANSWER: You can certainly do this, but there is no need to use the conditional format - I'm not 100% sure which events in VBA woudl be triggered by the update of the DDE link, but I would imagine a change event COULD be a likely candidate, so this would need to be programmed to check for you conditions and if met set the value you want - as it's vba it wouldn't need to copy and paste - it would simply be something like
if condition is true then sheets("sheet2").range("b2).value=sheets("sheet1").range("c3").value end if
this gives an idea of the kind of programming required. Let me know if I can help further - my email if it helps is email@example.com
---------- FOLLOW-UP ----------
QUESTION: Hi again Aidan thankyou for your responses they have been very informative but for some reason i am getting all sorts of error statements when entering my code. Could you please advise me how you would input the following data i have tried many different combinations but compile block if end if error statements keep appearing as well as object errors. I dont really know how to set up two conditions and what to write. the two conditions are. Len(Daniel.Range(J7).Value) = 6 and Daniel.Range(E40).Value > 0. Then if these are satisfied for Sheet1.Range(K19).Value = Daniel.Range(J20).Value sending me a bit nuts.
Sorry for the delay - it seems to have been a bit manic round here
sounds SOMETHNG like
if len(sheets("Daniel").range("J7").value=6 and sheets("Daniel").range("E40").value>0 then sheets("sheet1").range("K19").value=sheets("Daniel").range("J20").value end if
at the moment Daniel is followed by a fullstop, which means that Daniel is an object of some description, but probably not defined. You also need the quotes as I've shown here.
You have my email address so if it helps send me a file to check!