Simple Function to Find Value

Function code and screen shot of data shown below.

A Ratio (double) is passed into the function from the main body of the code. This Ratio is also the Advanced Ratio seen in the screen shot. The purpose of the function is to find a matching Advanced Ratio, and return its corresponding Cp. The reason I have the switch is because the data I have is only for intervals of 0.07, and this is how I thought to find the closest value to the inputted ratio (might be a much cleaner way).

I am getting a compiler error saying sub or function not defined. I am really rusty with coding. I placed this function right after the main Sub of the code. Not even sure if this function will return the Cp value? Any help is much appreciated.

Please Login or Register  to view this content.

http://i54.tinypic.com/4v2rdd.png

You don't appear to assign a value back to the function (cpcalc) and you probably need to use Application.Worksheetfunction.Lookup rather than just Lookup

Regards

Originally Posted by TMShucks

You don't appear to assign a value back to the function (cpcalc) and you probably need to use Application.Worksheetfunction.Lookup rather than just Lookup

Regards

Thanks!

I got a divide by 0 error for the condition of the If Statement. Apparently you can't mod by 0.07 so I upped the power of everything to a whole number.

Now it says unable to get the Lookup property of Worksheetfunction.

Please Login or Register  to view this content.

Ok.. so that happens when the value isn't found on the sheet, that was a math error in my main code not accounting for values external to my max and min ranges. Fixed that, encountered another issue. Going to do some error testing first before asking for more help

K this makes 0 sense. My math isn't wrong but the code says it is.

Please Login or Register  to view this content.

First time this goes through the Ratio = 2.5 so it goes to the Else and works fine. Second iteration Ratio = 2.58 so it goes into the If properly, but it displays "Shazam" when it SHOULD be setting Test Ratio to 2.57. If I am being unclear please let me know. I have been stuck on this for a while now..

It would help others to help you if you uploaded a sample workbook with some typical data and using your UDF.

Regards

What is the Mod operator supposed to be doing? Did you know it rounds its arguments to Longs before calculating?

The first time through the UDF it follows the ELSE route and doesn't go through the SELECT CASE so you get what you started with, 2.5. The second time through it goes through the SELECT CASE but again takes the ELSE option ... in otherwords, no value matches.

Now, I have to say, I've never seen SELECT CASE used in the way you have it here but that's not to say it's wrong, just that I've never seen it. You clearly don't really expect it to say Shazam (that takes me back) but what value do you expect?

Regards

Whatever the Select Case is supposed to be doing, I expect could be done more simply without, if you'd explain the logic you're trying to implement.

The select case was being used improperly I think. I found a much simpler solution with 2 If statements but it isn't accurate. I basically just changed the selects to ifs and it works fine.

Please Login or Register  to view this content.

I did a test with several dummy values and it returned what I wanted every time. Basically my Excel sheet has 101 data points from a graph that explains efficiency of wind turbines. I used GraphIt to get these points. Now I have to match up calculated values to the values from this graph. I'm matching a calculated Ratio value, to the given Ratio from a graph. The values in excel obviously don't include all possible values so the if block changes the calculated values to ensure they match the closest value possible. Then I need the function to return the Cp value that matches this Ratio value.

My Lookup Function is returning 0 now. Going to try editing it/using VLookup. Thanks for the help!

Edit: Oh and the Mod 7 is because in the Excel sheet you'll notice that the Ratio values go up by .007 (7 because I multiplied values by 100)