í have some value stored in worksheet as below

Name Sum Assuured Age Prem to be paid
Jacobs 300000 35 2879
Steve 400000 24 3686
Amit 400000 46 5954
Deepak 600000 78 15596
Ajay 300000 90 10347
Samuel 400000 55 5954
Tom 300000 65 5422
Russell 400000 72 10023

i have written a udf function as below :




Option Explicit
'Main Function
Function premium(ByVal MyNumber)
Dim Sumass As Long
Dim asage As Long
Dim prem As Long

Sumass = Cells(ActiveCell.Row, 2)
asage = MyNumber
Select Case Sumass
Case 300000
If asage < 36 Then prem = 2879
If asage > 35 And asage < 46 Then prem = 3188
If asage > 45 And asage < 56 Then prem = 4698
If asage > 55 And asage < 66 Then prem = 5422
If asage > 65 And asage < 71 Then prem = 7033
If asage > 70 And asage < 76 Then prem = 7865
If asage > 75 Then prem = 10347

Case 400000
If asage < 36 Then prem = 3686
If asage > 35 And asage < 46 Then prem = 4103
If asage > 45 And asage < 56 Then prem = 5954
If asage > 55 And asage < 66 Then prem = 6950
If asage > 65 And asage < 71 Then prem = 8989
If asage > 70 And asage < 76 Then prem = 10023
If asage > 75 Then prem = 13139


Case 600000
If asage < 36 Then prem = 4285
If asage > 35 And asage < 46 Then prem = 4823
If asage > 45 And asage < 56 Then prem = 7160
If asage > 55 And asage < 66 Then prem = 8393
If asage > 65 And asage < 71 Then prem = 10955
If asage > 70 And asage < 76 Then prem = 11970
If asage > 75 Then prem = 15596

End Select
premium = prem
End Function

in column 4 i have put the value = premium(c3) and dragged to the last row
but it is giving odd result but if i click on each cell and press enter it gives the correct result

i think i'm missing something in the udf, please help

Attached Files

Medical premium.xlsm (18.2 KB, 13 views)