Pricing labels

[hecgroups]

Hello Again,

Iam need to prepare an template for medical underwriting pricing tool.

Based on these following above first 4 fields it should calculate NRP then based on nationality the loading or discount will be appear in columns on sheet Template.

Original sheet is attached for your reference.

Please can anyone help me to make this file work as per requirement.

Thanks in advance for your help.

[arlu1201]

So from which sheet should the discount parameters be taken?

[JapanDave]

What Is NRP and how is it calculated?

[hecgroups]

Thanks for quick respond. This is the reason i like this site.

So, if you check the sheet3 you will find the details.

NRP means Net Rate Premium.

Check the sheet 3 if your find the few columns which contain the year between (Column a) then class wise i.e. VIP, A, B, C you will find relation i.e. Employee, Spouse & child.

my calculation based on sheet 3.

[arlu1201]

It will be good if you enter in some calculations in the template tab so we can find out how the calculations are done.

[hecgroups]

Thanks for advice i will do it right away

[hecgroups]

Kindly check the sample which i have done manually for 3 records. I hope this will help you to understand my requirement.

[Wieli]

Here is a code that should work, Its ugly and slow but that has todo with my limited knownledge in VBA, It works (i think as you want) and you can use it until you get a better version from someone.

[Wieli]

Here is a code that should work, Its ugly and slow but that has todo with my limited knownledge in VBA, It works (i think as you want) and you can use it until you get a better version from someone.

[hecgroups]

Thank you very much for your help although i don't have much knowledge in excel but you have make my life easier then ever. i will work on it and get back to you if i have any problem.

i hope their will be no problem.

[Wieli]

No problem, as i said its not an awesmome code since alot of it is hardcoded, which means you cant switch or add lines/columns and still get it to work properly. Just let me know if there is something that dosent work as you wanted.

[hecgroups]

I have problem in calculation. Kindly see the example below. it is taking nrp same for all the ages.

i have reviewed your script but did not find any errors. I hope you can help me in this problem.

updated sheet is attached for your reference.

[hecgroups]

they have removed internet access for me in my office that is why icannot provide u the update. anyhow please check this updated file and respond to my request.

[JapanDave]

Hey, Try this

Please Login or Register  to view this content.

[watersev]

hi hecgroups, as an option, formula only solution

G2:

Please Login or Register  to view this content.

H2:

Please Login or Register  to view this content.

[hecgroups]

Thank to Mr. Wieli, Mr. watersev & Mr. JapanDave for help me. i need a favor from you guys. kindly delete the attachments as it contain confidential information. If my management came to know about this file it may cause serious problem to me and i am sure i will lose my job.

Thanks for your efforts and your valuable help provided to me.

[watersev]

post #15 attachment deleted as requested

[hecgroups]

File is working perfect but still i have to check thoroughly. i will provide update after checking and marked solved if i have no issued.

Thanks to everyone one who help to resolved this issue. you guys make my life...

[hecgroups]

First of all i will thanks to you all who help me to setup this file. The file is working fine with other additional requirement but i am struck on below mentioned requirement.

This is my last request to finished up till issue.

It is possible to calculate all the members i.e. how many child, Employees & Spouses in age category by class wise and total premiums down in premium area.

Have a look on my new update file.

Sample file is attached for your easy reference.

[hecgroups]

Kindly find the file update file which need your help.

i have added one sheet (Loading Values) which need macros to add loading amount + in Template sheet need summary.

Need to calculate additional loading amount
Need summary of lives i.e. how many employee, Spouse & Child
Need calculation per member (Premium and loading) for Employee, Spouse& Child

Testing file is attached for your easy reference.

[JapanDave]

I am working on the file as we speak. I had a fair bit on at work today.

[hecgroups]

No problem take your time but just a request can it be finished tomorrow so that i can check wether it is working as per my requirement or not.

please use the updated file. Sorry for interruption.

Thanks for understanding my problem and help me.

[hecgroups]

Hai Mr. JapanDave,

Any luck for me.

[JapanDave]

I don't know what you want now? You have added sheets and different criteria. You will have to explain as clearly as possible what you want.

[hecgroups]

i am really sorry about changing the file. If we speak all together it will be mixup. so i will start one by one. OK.

i am very thankful to you that you have wrote VBA Macros to calculate the NRP based on nationality.

now i need help on counting no of employees at the same time it generate the report. i hope you understand this.

Sample file is attached for your reference.

If you help me up to set up this file i willbe very thankful to you.

[hecgroups]

Hello gMr. JapanDave,

i am having a problem with the file after changing the format of the cell. Can you help me to setup this issue.

Please Login or Register  to view this content.

After these changes the script is saying "Object variable or With block variable not set.

Copy of sheet is attached for your easy reference.

[hecgroups]

thanks for the responding

i have just change the columns which i have marked in code on post 26

[hecgroups]

Can any one help me please.

[hecgroups]

Can anyone help me please