detecting change in column without loop

[mandukes]

Hi,
I have values in column listed as following.

Is there any way to find if any value in column going less then the permissible limit?
For example:
I have a column A Which have variable data when making any changes effects values in column B
I want to set the constraint such that if column A making any values in Column B less than 6 (by increasing or decreasing value in column A ) then that value in column A will be rejected and Column A value will not update further (Increase or Decrease).

so the concept here is to loop through each Row in Column A and try to increase and decrease value until condition is satisfied.

Example 1:

[TABLE="class: grid, width: 150, align: left"]
<tbody>[TR]
[TD]Column A[/TD]
[TD]Column B[/TD]
[/TR]
[TR]
[TD]120[/TD]
[TD] 15[/TD]
[/TR]
[TR]
[TD]100[/TD]
[TD]12[/TD]
[/TR]
[TR]
[TD]100/50[/TD]
[TD]7/6[/TD]
[/TR]
</tbody>[/TABLE]







at 100 -------- 7 Acceptable
at 50 ----------6.5 Unacceptable
so the final value would be 100

Example 2:

[TABLE="class: grid, width: 150, align: left"]
<tbody>[TR]
[TD]Column A[/TD]
[TD]Column B[/TD]
[/TR]
[TR]
[TD]520[/TD]
[TD]15[/TD]
[/TR]
[TR]
[TD]400[/TD]
[TD]12[/TD]
[/TR]
[TR]
[TD]350[/TD]
[TD]11[/TD]
[/TR]
[TR]
[TD]200[/TD]
[TD]10[/TD]
[/TR]
[TR]
[TD]150/125[/TD]
[TD]10[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]8[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]7.5/5.5[/TD]
[/TR]
</tbody>[/TABLE]













If I decrease from 150 to 125 the value somewhere in column B goes down from 7.5 to 5.5 which is not acceptable.
Therefore, 150 will be accepted.

at 150 -------7.5 Acceptable `
at 125 -------5.5 Unacceptable

so, the final value would be 150


Thanks

 

[BrianJN1]

I am a little confused because you nominate a specific limit in your opening but one of your examples appears to contradict that.

Anyway,
if I enter 100 into A3 and 99 into A4, highlight those cells and drag them down to beyond 20 rows,
and then enter "=A3/14" into B3 and "=if(B3>6,A3,"")" into C3
and then drag those two columns downwards against the Col A values, there comes a point when no values show in Col C.

That therefore means that in your VBA your IF block will need to read something like:
= if calculated result >6 then
process
else
Last value
exit sub
end if

How that actually will work will depend upon your data and formulae which you have not disclosed.
As such this is very much a hypothetical solution.

 

[mandukes]

Here is the sample file : Sample

Try to edit Column A

I am a little confused because you nominate a specific limit in your opening but one of your examples appears to contradict that.

Anyway,
if I enter 100 into A3 and 99 into A4, highlight those cells and drag them down to beyond 20 rows,
and then enter "=A3/14" into B3 and "=if(B3>6,A3,"")" into C3
and then drag those two columns downwards against the Col A values, there comes a point when no values show in Col C.

That therefore means that in your VBA your IF block will need to read something like:
= if calculated result >6 then
process
else
Last value
exit sub
end if

How that actually will work will depend upon your data and formulae which you have not disclosed.
As such this is very much a hypothetical solution.

 

[BrianJN1]

I still don't understand what you are trying to achieve.

I've downloaded and unhide data. I see Col K appears to hold random values from 0 to 2.
It appears to me that the formulae down Col M do a Vlookup into Col O.

I wondered about the "13" and "14" in M2 and N2 and then realised that they are only column number references.

What I have come to understand is that if I increment/decrement the value in O3 I influence the values in Cols M:O. I notice that some of those O3 values may go below your limit of 6.

How are you inputting the O3 values? Do you somehow want a flag to appear at Q3 to indicate that you've gone too small because a value in O:O is below 6?

I doubt that I can assist further but your answers may prove productive for some other who may read.

 

[mandukes]

I purposely hide Col M andCol O because to be not confusing and it has no importance relating to the problem, and you don't have to change values in Column O3 instead I mentioned already to edit the value in Column "A" only which causing change in values in column "B".

I just want a way to adjust values in column A so that column B values be in the permissible limit. ( ie. not below 7 ). This can be achieved by changing values in column A and then checking all rows of Column B for permissible limit, to achieve this I need to loop through each row in column B every time value is changed , which can take more processing time (Slowing down Vba). I need to achieve this without looping.


Regards
~M