I have a range of cells on my spreadsheet that are subgroup averages of cells that are above them. What I need to do is put in a WorkSheet_Change macro that will display a Msgbox if there are 7 consecutive of these are negative numbers.
The Range is F74:Y74. It needs to display the message box if any 7 in a row are negative.
I really have no idea were to start with this one.
Hi
Here's one way
the change event
and the function
Please Login or Register to view this content.
HTHPlease Login or Register to view this content.
rylo
Hmm thats not working. any other ideas?
There would appear to be some ambiguity here (at least to me)
Originally Posted by schottwhittaker2333
So the first question would be whether it is indeed 7 consecutive negatives or just 7 negatives (title and opening would imply the former).
You state you want to use the Change event... so given the range in question (F74:Y74)
this will only work if the "cells above" (ie the precedents) are themselves altered manually, is that the case ?
In this instance it might be wise to post a sample file which apes your setup.
My Recommended Reading:
Volatility
Sumproduct & Arrays
Pivot Intro
Email from XL - VBA & Outlook VBA
Function Dictionary & Function Translations
Dynamic Named Ranges
Umm ya sorry, most people I know would agree that in a row is the same as saying consecutive, but maybe thats just were i'm from. anyway the cells above are not entered manually they are contected to other cells. I have attached the dummy worksheet. The row that is highlighted in red is the one that I am working with on this.
The Change Event does not fire as and when formula results update - it is invoked as a result of manual action.
You will need to revert to the Calculate Event ... obviously this means you are open to invoking the Event more often than is perhaps strictly necessary.
If this becomes an issue you might want to investigate Static variables or alternatively consider storing "prior" calculation values in a different range for comparative purposes (to current calculation values).
I think it comes down to interpretation - given the range you mention in your post is a horizontal vector the use of term "row" is somewhat ambiguous, no ?
I think that I can get around the calculate/ change issue however this does not appear to be were the problem is. The getcount() is not changing to true after 7 are consecutive.
Oh ya I suppose using the word "row" in this context when refering to excel is probably a good way to confuse people.
Last edited by scottwhittaker2333; 06-18-2010 at 01:21 PM.
Hi
Can you check your example file because when I put a number into F74 I immeditately come up with a circular reference error.
rylo
The circular refrence is because of the time stamp at the top of the subgroup. I just forgot to turn on the iterations in the sample workbook. Thats all.
Last edited by scottwhittaker2333; 06-21-2010 at 11:26 AM.
Hi
I opened your workbook, and turned on the iterations.
I then put the function into a general module in the workbook (copied from above) and the event code into the sheet1 code area (again copied from above).
I then manually put in -1 in the range F74:L74, one cell at a time, and the error message popped up.
Exactly what steps are you taking?
rylo
You got a circuler refrence worning from putting data into this range manully? I have no Idea why that would happen.
Ok I see it is working now. I do still have one problem. I need it to stop after it has happened once. And this seems to continue for each additional check afterwords. I tried adding cntr = 0 after the getcount = true but that didn't work. Any Ideas how to make it stop afterwords?
Hi
What do you mean by once? It will just keep producing the error message for each item that causes the change event to fire until the problem has been fixed. Are you going to continue with the error once the message has been produced? If so, then what action is going to start it off again?
Work through your scenarios and explain what you want to happen when, and how it all interacts.
rylo
The problem that I am having is that once there are 7 negatives it will continue to fire anytime weights are entered after that even if they are positive. I believe that the problem is that it is running through and checking the entire range every time new data is entered. However Now that I think about it it may be ok. I will have to talk to my boss about it but basicly because everything will have to be held anyway thay may no longher need to continue to take weights. I'm am not really sure as it is a senerio that is a little on the rare side. At any rate I would like to thank you for your help. You have been very very helpful and I can not thank you enough.
Please Login or Register to view this content.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks