Sara Hedtler
asked on
Transfer Worksheet Data to Userform & Calculate based on Sum Range
experts-exchange-userforms.xlsmI need to transfer data from specific cell content to my userform using a loops. I have 3 worksheets that feed off of each other. The maximum entry is 8 rows. I've attached the sheet with some of the codes I've used, but nothing I'm doing is worki ng.
Once the information is transferred, the userform will automatically calculate, the each finance company option will calculate what the interest rate will be based on the amount to be financed. Not sure how the variables will work.
This is the formula I use for each finance company:
Finance Co. 1: =IF(C8<2499,"8.250%",IF(C8
Finance Co. 2: =IF(G8<4999,"10.99%",IF(G8
Finance Co. 3: =IF(K8<999.99,"18.5%",IF(K
I don't know how to translate this in vba either.
Once the information is transferred, the userform will automatically calculate, the each finance company option will calculate what the interest rate will be based on the amount to be financed. Not sure how the variables will work.
This is the formula I use for each finance company:
Finance Co. 1: =IF(C8<2499,"8.250%",IF(C8
Finance Co. 2: =IF(G8<4999,"10.99%",IF(G8
Finance Co. 3: =IF(K8<999.99,"18.5%",IF(K
I don't know how to translate this in vba either.
Roy Cox
Why would you need to complicate it with a UserForm?
The code fails immediately in the example because it is trying to activate Sheet2, which doesn't exist. Also, activating a sheet is unnecessary.
ASKER
Because there are finance company options, payment plan options, etc. I haven't finished the form.
ASKER
Because there are finance company options, payment plan options, etc. I haven't finished the form.i had to set up 2 additional sheets, one for policies I'm proposing and the other for the polices that'll be finance.
This looks like the workbook we worked on previously. Which form does this question refer to and what data is used?
I'm off to work soon so I'll check back later
I do not see in the worksheets of the companies where is this formula. Where do you want it ?
Moreover this is a formula about interest rates with amounts and here are my remarks:
Company1 (presume it is PolicyList ???) you refer to C8 and Col C is Line of business does not make sence. So please clarify.
Comapny2 (presume it is Presenting ???) you refer to G8 and Col G is Agency Fee also does not make sence.
Company 3 (presume it is Financed ???) you refer to K8 and Col K is Stamping Fee also does not make sence.
So please clarify what are those formulas referring to by naming the column so we can help you better and what do you expect in the forms to happen at what field. !!!
gowflow
Moreover this is a formula about interest rates with amounts and here are my remarks:
Company1 (presume it is PolicyList ???) you refer to C8 and Col C is Line of business does not make sence. So please clarify.
Comapny2 (presume it is Presenting ???) you refer to G8 and Col G is Agency Fee also does not make sence.
Company 3 (presume it is Financed ???) you refer to K8 and Col K is Stamping Fee also does not make sence.
So please clarify what are those formulas referring to by naming the column so we can help you better and what do you expect in the forms to happen at what field. !!!
gowflow
ASKER
Sorry, it's a little confusing; we may have an account that has lets say 9 policies; maybe 5 of the 9 will be financed. If you look at the updated spreadsheet in the presenting page and the financing page; you'll see there are 4 not financed, and 5 that are, then if you look at the financing page, you'll see only the policies that will be financed. I need each line to go into the respective box, as shown in attached. 
It would be much simpler to use a ListBox instead of multiple TextBoxes
I have added code that copies data from the data on sheet 10.
I have only done the first 5 columns, but I am sure you can follow on.
Please note that your values in that sheet are not recognised as numbers so I have re-entered column F. The other amounts need checking.
Let me know if this is what you wanted
experts-exchange-userforms.xlsm
I have only done the first 5 columns, but I am sure you can follow on.
Please note that your values in that sheet are not recognised as numbers so I have re-entered column F. The other amounts need checking.
Let me know if this is what you wanted
experts-exchange-userforms.xlsm
ASKER
I tried to copy the exact way you did it for the Earned boxes and i received an error "Type Mismatch". I literally copied and pasted the code, i don't know what i did wrong?
I'll finish it off for you. I think it's because your entries on the sheet are not values, but seem to be Text
ASKER
The other boxes are working, just importing the "Earned" over. Also, the sum isn't calculating for some of the boxes; again copy and pasted. I did the sum formula in the worksheet itself and it came out as "$0.00"... I really have no idea why it's doing that. Copy-of-experts-exchange-userforms.xlsm
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
I can see Roy_Cox is handling it, I will not go into a rat race, however if you feel you are stuck somewhere, please do not spare me I will be glad to assist.
gowflow
gowflow
ASKER
Thank you! That worked perfectly. I removed the check for numeric values on the carrier/broker and lob cells, those will not ever have a numeric value. Once i did that, it worked like a charm! Thank you so much.
I saw the note on the modules; i appreciate you telling me i could use one for what i needed. The one I wasn't able to understand was the Autofilter or advanced AutoFilter. I'd love not to have to click an "Update Button" to make sure each sheet is automatically updated. I noticed that when i did a change to one of the policies on the policy list, it would not update the form, it would just place another line at the bottom of each page.
I saw the note on the modules; i appreciate you telling me i could use one for what i needed. The one I wasn't able to understand was the Autofilter or advanced AutoFilter. I'd love not to have to click an "Update Button" to make sure each sheet is automatically updated. I noticed that when i did a change to one of the policies on the policy list, it would not update the form, it would just place another line at the bottom of each page.
ASKER
Thank you GowFlow, I'm sure I'll get stuck again since I am so absolutely new to this!
ASKER
I need to know what I'm doing wrong. I don't understand how I'm unable to get the calculations done. Copy-of-experts-exchange-userforms1.xlsm
What calculations? What does the Total Cost, etc refer to on the left of the userform?
Are you calculating 20% of the Pure Premium Totals, etc.?
Are you calculating 20% of the Pure Premium Totals, etc.?
ASKER
We calculate the pure premium and taxes for the percentage, then the earned is added for the total down payment. The down payment is typically 20%, but it can change. If a policy has a filing, the down payment is 25.6%. I just put a value in there to calculate the down payment.
I'm trying to understand if you need to waht the column of TextBoxes in the top left are supposed to calculate. Is this to be based on the Total Row below?
ASKER
Sorry, it's a little (a lot) confusing. 
Hopefully, this will make sense.
Hopefully, this will make sense.
ASKER
I think I'm getting close! I still can't get the amounts to tally up at bottoms. I'm now working on the pmt functions and if statements for the interest rates based on the amount of the loan. Copy-of-experts-exchange-userforms1.xlsm
I'll have a look and see what you have done.
I've moved the calculations that you have added to the LoadCells Procedure. Check this and let me know if this is what you expect, I'm not sure if the downpayment is calculated correctly.
Copy-of-experts-exchange-userforms1.xlsm
Copy-of-experts-exchange-userforms1.xlsm
I have added a calculation for the monthly payments.
Copy-of-experts-exchange-userforms1.xlsm
Copy-of-experts-exchange-userforms1.xlsm
@bizinsurancepro
... sorry but this is not 'a Question' anymore but rather a project. Believe you should ask specific questions and when answered award points and move onto a new question hence keeping threads to minimum order not loose potential readers facing similar issues.
gowflow
... sorry but this is not 'a Question' anymore but rather a project. Believe you should ask specific questions and when answered award points and move onto a new question hence keeping threads to minimum order not loose potential readers facing similar issues.
gowflow
Is this working as you want?