I'll tell you what I see, Deepak. When I open your workbook, the first thing Excel does is tell me that editing is disabled. (That's because I downloaded it from the internet; MS Office is protecting me, you see.) I click the button to enable editing, and it then warns me about embedded macros. I select the option to enable the macro, and I see column 4 is all #VALUE. But when I paste R2C4 over R2C4:R18C4, it fills in the values correctly. So far, so good. I don't think that's an error; it was just temporarily confused because the worksheet didn't have macros enabled at first. At least, that's what I surmise.

There is something wrong, though, or sort of wrong. If you change a value in C3, the premium in C4 is updated correctly; but if you change a value in C2, C4 remains unchanged. This is because Excel can see that RC3 is an argument to the call to premium(), so when RC3 changes then RC4 needs to be recalculated. But Excel cannot tell by looking at the arguments to =premium that RC3 is also part of the equation.

You should fix that, and there are two ways. One is to rewrite your UDF so that both RC3 and RC4 are arguments to Premium, like this:(I don't care whether you use my Case structure or stick to your If statements; I'm just demonstrating that you can use two arguments in the program.) Your call in C4 would then say "=Premium(RC2,RC3)", and the value displayed there would change whenever you changed either C2 or C3.

But the way I'd do it is to set up a table in another sheet and use lookup formulae to find the proper premium on that table. I like programming in VBA, but this looks to me like a case for a table, instead. Take a look at the sample I've uploaded. There are lots of ways to do the lookup—and by the way if you ask macropod he'll probably come up with a better way than the one in my example, he's a whiz at that sort of thing—but I think that for this sort of application, this is better than writing a UDF.

Attached Files

x.xlsm (18.5 KB, 10 views)