+ Reply to Thread
Results 1 to 19 of 19

code stops working when i put a formula in the cell

  1. #1
    Forum Contributor
    Join Date
    05-18-2011
    Location
    Delhi , India
    MS-Off Ver
    Excel 2007
    Posts
    171

    code stops working when i put a formula in the cell

    the following code works well for me but when i put a formula in A1 or link it from somewhere it stops working

    Please Login or Register  to view this content.
    plz help me convert this code

  2. #2
    Forum Expert JBeaucaire's Avatar
    Join Date
    03-21-2004
    Location
    Bakersfield, CA
    MS-Off Ver
    2010, 2016, Office 365
    Posts
    33,492

    Re: code stops working when i put a formula in the cell

    Worksheet change only works off of physically triggered manual changes in cells. If you want this code to trigger every time the FORMULA in cell A1 changes value, then you'll need a different approach.

    Is that what you want? If A1 ever changes value, trigger this "log" event? By the way, WHAT is in A1? A number, a decimal number, a text string?
    _________________
    Microsoft MVP 2010 - Excel
    Visit: Jerry Beaucaire's Excel Files & Macros

    If you've been given good help, use the icon below to give reputation feedback, it is appreciated.
    Always put your code between code tags. [CODE] your code here [/CODE]

    ?None of us is as good as all of us? - Ray Kroc
    ?Actually, I *am* a rocket scientist.? - JB (little ones count!)

  3. #3
    Forum Contributor
    Join Date
    05-18-2011
    Location
    Delhi , India
    MS-Off Ver
    Excel 2007
    Posts
    171

    Re: code stops working when i put a formula in the cell

    yes sir thats exactly what i want ... A1 contains a formula (=ROUND(G1/5,0)*5) , where G1 is running live stock market rate.

  4. #4
    Forum Expert JBeaucaire's Avatar
    Join Date
    03-21-2004
    Location
    Bakersfield, CA
    MS-Off Ver
    2010, 2016, Office 365
    Posts
    33,492

    Re: code stops working when i put a formula in the cell

    Try this instead:

    Please Login or Register  to view this content.
    Last edited by JBeaucaire; 09-20-2011 at 03:29 AM. Reason: corrected number format

  5. #5
    Forum Contributor
    Join Date
    05-18-2011
    Location
    Delhi , India
    MS-Off Ver
    Excel 2007
    Posts
    171

    Re: code stops working when i put a formula in the cell

    its working , but now after the conversion it has started behaving differently.

    in the first code it used to copy the change in cell (when done manually) to B2 , then when it changes again to B3 and so on.

    now somehow its copying the change in cell to B2 till B98.

  6. #6
    Forum Expert JBeaucaire's Avatar
    Join Date
    03-21-2004
    Location
    Bakersfield, CA
    MS-Off Ver
    2010, 2016, Office 365
    Posts
    33,492

    Re: code stops working when i put a formula in the cell

    What conversion?

  7. #7
    Forum Contributor
    Join Date
    05-18-2011
    Location
    Delhi , India
    MS-Off Ver
    Excel 2007
    Posts
    171

    Re: code stops working when i put a formula in the cell

    conversion as in the new code which you have written

  8. #8
    Forum Contributor
    Join Date
    05-18-2011
    Location
    Delhi , India
    MS-Off Ver
    Excel 2007
    Posts
    171

    Re: code stops working when i put a formula in the cell

    its working well with the change in value of the formula but its doing something different now when the change in A1 occurs

  9. #9
    Forum Expert JBeaucaire's Avatar
    Join Date
    03-21-2004
    Location
    Bakersfield, CA
    MS-Off Ver
    2010, 2016, Office 365
    Posts
    33,492

    Re: code stops working when i put a formula in the cell

    I don't understand what you're saying. The code simply watches the value in A1. If it changes, it registers the new value in column B with a timestamp.

  10. #10
    Forum Contributor
    Join Date
    05-18-2011
    Location
    Delhi , India
    MS-Off Ver
    Excel 2007
    Posts
    171

    Re: code stops working when i put a formula in the cell

    i am having some problem running the code in live feed. it starts copying the value of A1 in column B till B98 and then excel crashes.

  11. #11
    Forum Expert mikerickson's Avatar
    Join Date
    03-30-2007
    Location
    Davis CA
    MS-Off Ver
    Excel 2011
    Posts
    6,229

    Re: code stops working when i put a formula in the cell

    Please Login or Register  to view this content.
    Last edited by mikerickson; 09-20-2011 at 04:35 AM.
    _
    ...How to Cross-post politely...
    ..Wrap code by selecting the code and clicking the # or read this. Thank you.

  12. #12
    Forum Expert JBeaucaire's Avatar
    Join Date
    03-21-2004
    Location
    Bakersfield, CA
    MS-Off Ver
    2010, 2016, Office 365
    Posts
    33,492

    Re: code stops working when i put a formula in the cell

    I'll be happy to examine your workbook.

  13. #13
    Forum Contributor
    Join Date
    05-18-2011
    Location
    Delhi , India
    MS-Off Ver
    Excel 2007
    Posts
    171

    Re: code stops working when i put a formula in the cell

    it still isnt working on the live feed . when i link A1 with G1 then the program works perfect but when i link it with H1 (live feed) then it doesnt respond. i have attached the file.
    Attached Files Attached Files

  14. #14
    Forum Expert JBeaucaire's Avatar
    Join Date
    03-21-2004
    Location
    Bakersfield, CA
    MS-Off Ver
    2010, 2016, Office 365
    Posts
    33,492

    Re: code stops working when i put a formula in the cell

    You're using something external, Chart.exe, that I cannot troubleshoot.

    You're using Mike's macro, so I'll let him respond. Hope you figure it out.

  15. #15
    Forum Expert mikerickson's Avatar
    Join Date
    03-30-2007
    Location
    Davis CA
    MS-Off Ver
    Excel 2011
    Posts
    6,229

    Re: code stops working when i put a formula in the cell

    Apparently the live feed doesn't trigger a Change event. It looks like JBeaucaire's code would be the way to go.

  16. #16
    Forum Contributor
    Join Date
    05-18-2011
    Location
    Delhi , India
    MS-Off Ver
    Excel 2007
    Posts
    171

    Re: code stops working when i put a formula in the cell

    i found a function

    Please Login or Register  to view this content.
    it isnt doing the trick on the live feed , perhaps you could alter it

  17. #17
    Forum Contributor
    Join Date
    05-18-2011
    Location
    Delhi , India
    MS-Off Ver
    Excel 2007
    Posts
    171

    Re: code stops working when i put a formula in the cell

    i found a function

    Please Login or Register  to view this content.
    it isnt doing the trick on the live feed , perhaps you could alter it

  18. #18
    Forum Expert JBeaucaire's Avatar
    Join Date
    03-21-2004
    Location
    Bakersfield, CA
    MS-Off Ver
    2010, 2016, Office 365
    Posts
    33,492

    Re: code stops working when i put a formula in the cell

    Why would I? That function seems non-relevant and the code I gave is giving me timestamps down my sample workbook forever, until I turn it off.

    Your crashing problem is being caused by something as yet undisclosed, possibly in that CHART.EXE it seems to reference. The macro provided is very basic and does the job described.

  19. #19
    Forum Contributor
    Join Date
    05-18-2011
    Location
    Delhi , India
    MS-Off Ver
    Excel 2007
    Posts
    171

    Re: code stops working when i put a formula in the cell

    ok thanks sir ... i tried using it in another feed but it crashed again ... maybe some problem with the live feed ... i appreciate your time .

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1