Macro to find value between two limits for a given condition

[DamirDz]

Hello everyone!

I'm hoping anyone can help me with a problem. I need a macro that finds me a certain value bewtween two preasigned values while a certain condition is met.
Now these are my values from a sheet in excel

Please Login or Register  to view this content.

All these values are known and fixed except Nrd; and i'm looking for ec while Nrd=Ned. The value Nrd depends on the value k, hence it depends on the value ec. The condition for ec is that it must be 0>ec>ecu (ecu is negative). Nrd is calculated in excel in table form.
I introduced a value G that is an alloved error for the result like abs(G)=abs(Nrd)-abs(Ned). So instead of Nrd=Ned the condition can also be satisfactory if G<0.1 (I assumed this if the calculation takes alot of time), so it's a like a precision limit for ec.
If needed the ec value can go in a step of let's say 0.0000001 (or smaller if doesn't take too long to calculate).

Thanks in advance!

DamirDz

[patel45]

attach please the file

[DamirDz]

Dear patel45

Here's the file
Mi-Fi.xlsm
or here
http://www.4shared.com/file/yrSEXXOL/Mi-Fi.html

Thank you!

DamirDz

[patel45]

I can not find any code in your attachment

[DamirDz]

Dear patel45,

Here's the file
Mi-Fi_1.xlsm

This what I only wrote, i had few tries with if and do functions but i got stuck.

Thanks!

DamirDz

[patel45]

Please Login or Register  to view this content.

[DamirDz]

Thank you very much for your time and effort patel45.
It would be nice if when have have time just to explain what you exactly did here since I'm new to VBA.

Thanks again!

DamirDz

[patel45]

Does it solve your problem ?

[DamirDz]

Yes it does!
Thanks again!

Just one more question: what if my value ec is bounded like this 0<ec<ecmax?

Best regards!

DamirDz

[patel45]

I did not understand your question

[DamirDz]

Dear patel45,

I do have some problems, but it's my mistake. The condition doesn't satisfy in some cases because i set the "G" value wrong. this value should be 1.00005<Nrd/Ned<0.99995 instead abs(Nrd)-abs(Ned)<0.1 (or Nrd/Ned=1 in a perfect calculation) this way the function makes sure that Nrd and Ned have the same sign (they can only be both positive or both negative).

I added one more calculation (i copied your code twice) but this time I'm looking for the value es which can be -0.002<es<esmax (esmax=0.025). But I cannot figure out how to modify the code for this range it blocks at some calculations. In this calculation i used "H" instead of "G", but this should also be Nrd/Ned.
I'm attaching my file again if you want to see what I did. My english not very well to explain it properly

Mi-Fi_2.xlsm

Thank You!

DamirDz

[patel45]

I'm sorry but there is too much confusion for me, I'm seeing 2 calculations in your sheet.

[DamirDz]

Yes there are 2 calculations: one is looking for ec betwenn 0>ec>ecmin while es is fixed; and the other is looking for es between 0<es<esmax while ec is fixed.
The calculations are very similar and I have copied everything on the sheet and use different cells for each calculation. In general they are the same. Essentially I used your code twice and nothing more.
I do only have problems with looking for es : sometimes it makes a correct calculation for certain Ned and sometimes it blocks and cannot converge.

[patel45]

did you try using Solver ?
http://office.microsoft.com/en-us/ex...001124595.aspx

[DamirDz]

Yes I did, and the solver function works perfectly, but the problem is I cannot "run" solver from other sheets. The excel file here is just a sheet in a bigger excel file I'm making.

I wanna thank you for your time and effort patel45!

Best regards!

DamirDz