Macro for finding a specific value by comparing two columns

Hi,

I am new to VBA and thinking if there is a more elegant way to achieve what I am trying to do in the code.

I am trying to find the value of "Q" ("C18") at which any value in column "D" is greater than the corresponding value in column "B" ?

Basically can I use "For i = 1 to 5" instead of having to write down each cell address to get the required value of "Q" ? My code is as follows. Any help would be appreciated. Thanx

Please Login or Register  to view this content.

File attached.

Thanx much for helping me out.

Okay if the value of C5 is greater than the values in column D, so what then?

No, if the value of any cells of column D is greater than the corresponding value in column B , then that specific value of "C5" is the answer because values in column B are being calculated by using value "C5".

Thanx

Macro?

C18:

=SUMPRODUCT(($D$8:$D$12>$B$8:$B$12)*1)

That is not what I am doing.

Again,

Value in "C5" changes the values in Column "D". Then Column "D" is compared with column "B" and if any value of column "D" is greater than the corresponding value in column "B", then that "C5" value is the answer. If after one iteration, none of the values in column D are greater than the corresponding values in column B then the value of "C5" is increased by "1" that is "C5" + 1 = New "C5" and the values of column D are computed again and then compared with corresponding values in column B and so forth till the result is achieved.

Hi, Thanx for the code. But it is not giving me the answer I want. I am trying to that value of "C5" at which any One value in column "D" is greater than the corresponding value in column "B".

The correct answer is "52" and at this value of "C5", only one value of column D is greater than the corresponding value of column B.

Many thanx for your help if you can guide me in the right direction.

Hi, Thanx for the code. But it is not giving me the answer I want. I am trying to that value of "C5" at which any One value in column "D" is greater than the corresponding value in column "B".

The correct answer is "52" and at this value of "C5", only one value of column D is greater than the corresponding value of column B.

Many thanx for your help if you can guide me in the right direction.

Hi, thanx Patel4, I tweaked your code to get the desired result. Now it works. Many Thanx

Please Login or Register  to view this content.

Originally Posted by Maxro

The correct answer is "52" and at this value of "C5", only one value of column D is greater than the corresponding value of column B.

Both code and the formula are working as they should

Thanx for that jindon.