Check if all values in Array are greater or less than specific value in worksheet

[jprlimey]

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.

[kasan]

Put value from cell in some variable and then loop through array and compare, like this:

Please Login or Register  to view this content.

[pjwhitfield]

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

Please Login or Register  to view this content.

would give you the answer directly from the range

[jprlimey]

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

[jprlimey]

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.

[jprlimey]

Still trying to solve this, does anyone else have more input?

[kasan]

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.

[xlnitwit]

Please Login or Register  to view this content.

[jprlimey]

Thanks guys for input.

Please Login or Register  to view this content.

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.

Likewise for the

Please Login or Register  to view this content.

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.

[xlnitwit]

If the smallest value is greater than A1, the other values must be also. There is no need to test them all.

[jprlimey]

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.

[jprlimey]

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.