I used the below code to populate an array with values from active cell up 8 rows. I now want to check if all values in the array are either greater than or less than a specific value in worksheet, let's say the check value resides in cell "A1" for simplicity.
Any help would be greatly appreciated.
Please Login or Register to view this content.
Last edited by jprlimey; 01-25-2017 at 08:45 AM. Reason: Solved
Put value from cell in some variable and then loop through array and compare, like this:
Please Login or Register to view this content.
I take it you want a single result like TRUE/FALSE rather than each individual? ie if 7 of the 8 are greater than A1 then its FALSE but if all 8 are then TRUE?
Do you need the data in an array for something else or was it just put in there to do this task because
would give you the answer directly from the rangePlease Login or Register to view this content.
If someone has helped you then please add to their Reputation
Yes, I'm triggering the check on an Worksheet_Change event
Yes I only want the MsgBox to display when ALL values in array are greater than A1
kasan,
I tried your suggestion. It did loop thru the array to check the value in cell "A1" against array values. However it returned the MsgBox for each occurrence of the array value being > than "A1" value. What I need is MsgBox when ALL records in array > value in cell "A1"
Thanks
Please Login or Register to view this content.
Still trying to solve this, does anyone else have more input?
If all values is > A1 then value of flag will remain 0 and you will see your MsgBox, if at least one value is <= A1 then nothing will happen
Please Login or Register to view this content.
Please Login or Register to view this content.
Don
Please remember to mark your thread 'Solved' when appropriate.
Thanks guys for input.
Didn't work, it does check the 1st value in the array and verifies >= to rad value, however because the if statement is true, the flag is set to 1 then it exits the for loop, but it only checked the 1st value in the array. I need to check that all nine values in array are >= rad value.Please Login or Register to view this content.
Likewise for the
It looks for the min value of the array and checks if > value in "A1", again we are not looking at all 9 values in the array.Please Login or Register to view this content.
If the smallest value is greater than A1, the other values must be also. There is no need to test them all.
Oops, yes you are correct. I didn't think that through before posting my response!
I tested the solution below and it work great, thanks for the support.
Please Login or Register to view this content.
Oops, yes you are correct. I didn't think that through before posting my response!
I tested the solution below and it work great, thanks for the support.
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