VBA Macros Recording Script

[hecgroups]

Hello,

Below is the script which is recorded VBA Macros.I need help to write a proper script and actual calculated value should be appear in the cells not the formula itself.

Can it be possible.

Note:This topic already posted HERE

Please Login or Register  to view this content.

[royUK]

That code would not work in Excel 2003, it uses SUMIFS which were not available in that version.

Best thing to do is state what you want to achieve, not post code taht you think works.

[hecgroups]

Actually it is a part of my script i will attached the file itself for your easy reference.

Here is the explanation what i am trying to do.

Once all the Test()i.e. macros finished calculation the average premium will appear in range M13:M16, O13:O16 and Q13:Q16.

[hecgroups]

can some one help me

[watersev]

hi hecgroups, try the following (I've not tested it):

Please Login or Register  to view this content.

PS. It would be wise to update your profile with Excel versions you are using as it arises questions

[hecgroups]

Nice to see you again Mr. watersev.

It is very close to my request values is showing in a result instead of formula but the calculation result is wrong.

[hecgroups]

I have tried your script and recheck the formula everything is correct but the result is wrong.

[watersev]

I was basing on the formulas entered in range in question without checking its logic. Try to amend formulas in my code with your recorded code (if it gives correct formula calculation), posted in post #1. Example:

Please Login or Register  to view this content.

becomes

Please Login or Register  to view this content.

[hecgroups]

It doesn't work. It is not displaying any value now.

Please Login or Register  to view this content.

[watersev]

try this out:

Please Login or Register  to view this content.

[hecgroups]

Everything this is OK again the result is wrong. I have compair the formula with the original one there is not mistake. Then where is the mistake happening.

[hecgroups]

Hai Mr. watersev

I have managed to get the result correct but only on last range i.e.Q13:Q16 i am getting #N/A

Here is the updated script

Please Login or Register  to view this content.

[watersev]

the formulas were wrong initally, correct ones:

Please Login or Register  to view this content.

[hecgroups]

Still wrong check details below.

Please Login or Register  to view this content.

Please Login or Register  to view this content.

[watersev]

is it results got on the sample file you've posted in reply #3? Did you use the code from post #13?

[hecgroups]

Yes this is the result after using the script in post #13. If you don't mind can you ask for second openion

[watersev]

what do you mean by "If you don't mind can you ask for second openion"?

What result do you get on a posted file using your formulas and code from post #13?

Can you provide a file giving discrepancy in formulas and script results? I wnat to see the formulas you are using to get your results.

[hecgroups]

Sure i will. In fact it is easy to understand and get both of us on same page. There you go. File is attached

[watersev]

the formulas in my code were correct, the problem was caused by operation transforming formulas to values. Corrected code:

Please Login or Register  to view this content.

[watersev]

a bit shorter code option:

Please Login or Register  to view this content.

[hecgroups]

Thanks for your most valuable help & time which you have wasted for me. I have one more request which is related to fame file.

Can you do the same for the below script please. Same issue i don't want to display the formula I want to display values itself.

Please Login or Register  to view this content.

[watersev]

hi there, if I'm not mistaken, this code is a part of a Test code, the new part underlined and commented:

Please Login or Register  to view this content.

[hecgroups]

Excelent work.It is working and you make it possible. Thanks for your help. I will provide you feed back after working on my original file. I am sure everything will goes well. If anything goes wrong i will come back to you.

[hecgroups]

New requirement from my boss. It goes like this.

If a child (Male/Female) under 24 years same calculation. But if the child (Female) over 24 years it should take the premium and calculate in the new column i.e. Column S13 to S16.

If the child over 24 years (Male) then the premium becomes zero automatically.

Calculation of loading. Class VIP only employee take total NRP divided by new NRP i.e. Column G/Column J

I know it is little bit confusing but hope that i have explained to you very well. I am attaching the file for your revision.

[watersev]

please check attachment

re: loading, I do not understand how to calculate it from your explanation. For Vip Employee I get 0.92, you have 8.6. The rest types are unclear as well.

[hecgroups]

I will get back to you as soon as possible whit detail explanation.

[hecgroups]

The result is OK. You are getting 0.92 because the format is different. If the same result change into % format you will get 8.60 or 8.62 whaterver.

1 more issue. If the child over 24 years (Male) then the premium becomes zero automatically. Still the list showing the values although it should be "0" and should not calculate anyware. i.e. Neither in NRP Nor in loading.

[hecgroups]

Kindly see the attached file i have change the range in template sheet the nrp is not working.

This will be final setup of my file.

Sorry for inconvenience.

[hecgroups]

Mr. Watersev. today i have fixed the small errors which i am facing. Now only thing is pending in this file is one condition which is if the child male over 24 years the the NRP should become zero. If you just fix this condition i will do the rest.

In case if i cannot do the rest of the fixing formula then i will come back to you again. Due to this project i have learn lot of things.

[watersev]

I'm confused with what you need

[hecgroups]

There is not confusion. I need to make the premium nil if a male child whose age is over 24 years. I means, even the premium is entered and after running the script it should make the value nil.

Please check row no.10 in template sheet whose NRP is 50 but after running the script it should become nil means 0 i.e. Zero.

[watersev]

code:

Please Login or Register  to view this content.

[hecgroups]

It is not taking NRP but it is taking other amounts which is the last 2 columns i.e. column I & J. I think we have to fixed the nationality column also because this script based on Nationality also. In between if you get time just fix this issue. I will try to fix this issue also if i sucessed i will let you know.

Thanks to you for tolerating me. I really appreciate...

[hecgroups]

Hai Watersev,

I have manage to get the achieve what i want.

[watersev]

please attach the file with formulas in those cells

[hecgroups]

I am sorry. Here is the attached file as per your request.

[watersev]

please check attachment, formulas have been added to NetRiskPremiums2 code. I've changed them from array formulas to sumproduct

[hecgroups]

Hai watersev up till now it is working fine Thanks for your valuable help. And a request can you please delete the attached file from your side. Topic marked as solved and reputation added.

[watersev]

thanks, do mean the last posted file only or all attachments in the thread posted by me?

[hecgroups]

If you have time remove all the files which you have attached. thanks for your support.

[watersev]

everything is done as requested, all attachments deleted

[hecgroups]

Hai watersev i have an issue with the same problem but different formulas. Can i post it here for your because you know the logic and you will not spent lot of time to fix it. If yes then i will post it here otherwise i will open a new topic.

[watersev]

hi, I would suggest to start a new thread

[hecgroups]

OK thanks for your help