#Value error in Excel Public Function

[waholtmn]

Good Afternoon,

I keep getting the #Value! result using the syntax below. I'm new to writing user defined functions so the solution is not easy for me. Please help.

Please Login or Register  to view this content.

[JosephP]

last line should be

Please Login or Register  to view this content.

[stubbsj]

Welcome -

You need to declare Ret

Please Login or Register  to view this content.

Also, you need to embed you code on the post as code or the Moderator Cops will bust you. Explained in the rules. http://www.excelforum.com/forum-rule...rum-rules.html

[waholtmn]

I tried both of those suggestions and received the same result. Month 1 and Month 2 are numbers and SvcDescr is text. The first line is to see if SvcDescr is a certain value "Filter/Block" then see if month2 is either greater than 17 but less than or equal to 29. If both are true, then do the calculation and if not true go to the Else statements.

I have a simple table setup to test the function. A1 = Service Description B1 = Month 1 c1 = Month 2 d1 = month 3 and e1 = FORECAST
A2 = Filter/Block B2 = 47 C2 = 17 D2 = 17 and E2 is where the function result is expected.

Please Login or Register  to view this content.

[stubbsj]

Try

Please Login or Register  to view this content.

[stubbsj]

This should work. You can't use FORECAST because it is an Excel Formula. You need to create a private function. Also, why are you passing Month1 but never use it in your function?

Please Login or Register  to view this content.

[waholtmn]

Thanks I'll give it a try when I get into the office tomorrow. I use month 1 by adding it in at the end of the final expression....12 * 11 + Month1

[JosephP]

or perhaps

Please Login or Register  to view this content.

[waholtmn]

Thanks...both of the suggestions work and now I want to throw a new wrinkle in...Instead of multiplying by "29", "17" or "12"...I want to multiply the average of month2 and month3 by 11 and add back in month 1.

I have a simple table setup to test the function. A1 = Service Description B1 = Month 1 c1 = Month 2 d1 = month 3 and e1 = FORECAST
A2 = Block/Filter B2 = 47 C2 = 10 D2 = 20 and E2 is where the function result is expected.

ORIGINAL CODE

Please Login or Register  to view this content.

Or this code

Please Login or Register  to view this content.

[JosephP]

I reckon

Please Login or Register  to view this content.