Find closest value from Range

Dear Excel Gurus,

I had created a simple macro with looping in columns C,D,E by formulas. In column H it returns "0" or "1" depends on 2 conditions in columns F,G. I tried my best here

Please help me to find value in column C, which is closest to "1" always IF in column H also "1". On next step just return corresponding value from column B.

Any optimizations and improvements to my code are only welcome. For me the best is to avoid additional columns F, G, H and have all calcs inside the code. (my file is attached)

I would be appreciate for any input. Thx.

Code:

Please Login or Register  to view this content.

Hello!

You are right, reverse iteration only for my testing...of course it can start from i = 1. No problem, I will try to explain the logic: column B contain always input data, eg for i=1 macro takes value from cell B2 --> calc C2 --> calc D2 -->calc E2 (by integrated formulas), then check two conditions: if D2>=10^4 then F2=1 else 0, IF E2>=1.15 then G2=1 else 0. Last calc in column H is logical AND of columns F and G --> next row i+1.

Final goal - is to check full column H, if it's contains "1", after that return closest value as I described before. So, FINAL RESULT should comes from column B based on column H (if=1) and C (value closest to "1" always)

I hope now this task is more clear for you.

Thank you for support!

In reality no, I just need to iterate by column B and find final result.

Thank you, but i need solution by macro. How to find by formulas is completely clear for me

Awesome, my friend!!! Everything works as should be, shake your hand

Sorry, i forgot one thing: how I can return corresponding value from column B? In that case it should be = 38.4

Brilliant!!! thank you so much