i'm having a macro runing depending on the value of the cell I1, every time the value in I1 changes, i want the correponded dependent cell values change, there is one case that i want to clear the contents of certain range but in this case the code happens to run into an infinite loop and i have no idea why here is my code, hope someone could help! thanks all in advance!
Please Login or Register to view this content.
Last edited by gloger; 07-27-2015 at 04:57 AM.
Firstly, your title and the description of the issue doesn't seem to match. So are you experiencing a runtime error or an infinite loop?
Secondly, for some unknown reason, it seems you have edited your code to look the way it is. However, it seems that your code is actually a Worksheet Change event. As such, when the macro you wrote changes something within the sheet "Range.Value" or "Range.ClearContents" it will trigger the Change event macro again. Causing the infinite loop. As far as I know, Excel has a built in safe-guard against these kinds of infinite loop, so I'm not sure why you are having it. In any case, to prevent it, you'd need to disable EnableEvents before your code makes any changes to the model. Example...
Please Login or Register to view this content.
please excuse my bad description
run time error is the bug that comes with the macro, i said infinite loop because it seems that the program couldn't stop and the screen is blinking
here is the original code and i was using worksheet calcuate actually.. however, this code is not working automaticammy like yesterday when i first tested it:::so werid;.
Please Login or Register to view this content.
When exactly do you want this code to run?
The first code you posted would need to be run manually, and the second code you posted would run whenever the sheet calculated.
As for the run-time error, I can't replicate that.
Is the sheet protected?
If posting code please use code tags, see here.
i want it to run whenever the value in cell I1 changes,
the sheet is not protected
Does this code, which belongs in a worksheet module, work?
Please Login or Register to view this content.
no it is not working at all..
no actually it worked with a new sheet i just created but not the previous one and i dont know why...
ok. the thing is that , cell I1 is actually a formula and its value changes by its formua, but if i change I1 manually, then your code works just perfectly
Last edited by gloger; 07-24-2015 at 09:22 AM.
Any chance you could upload a sample workbook?
Click on GO ADVANCED and use the paperclip icon to open the upload window.
thank you Norie, the problem is solved, i copied the sheet contents little by little and found there was a mistake inside a table, i don't know that the mistake is, but i recreated the table and then the code worked fine.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks