Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
How to write a macro
I have never written a macro before and believe that using one might help my
problem. People using the form I'm creating will enter their income in one cell and then choose one of the following from a drop down menu in another cell: yearly, monthly, twice per month, per 2 weeks, per week. I need to convert the income totals to the same format (yearly) before adding the numbers together in a new cell for a total (income totals will be multiplied by 12, 24, 26 or 52 depending on their choice from the drop down menu). There is the potential for 25 different incomes to be entered on this form. Any help you can provide is greatly appreciated! Thanks. |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
How to write a macro
First, look here http://www.mvps.org/dmcritchie/excel/getstarted.htm
-- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) "JStiehl" wrote in message ... I have never written a macro before and believe that using one might help my problem. People using the form I'm creating will enter their income in one cell and then choose one of the following from a drop down menu in another cell: yearly, monthly, twice per month, per 2 weeks, per week. I need to convert the income totals to the same format (yearly) before adding the numbers together in a new cell for a total (income totals will be multiplied by 12, 24, 26 or 52 depending on their choice from the drop down menu). There is the potential for 25 different incomes to be entered on this form. Any help you can provide is greatly appreciated! Thanks. |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
How to write a macro
You should be able to do it without a macro if your "form" is a regular
worksheet (which I'm assuming because you said entries are in cells). A1 has the income entry B1 has the list which has same entries as you'll see in this formula, which I put into C1 (remember that the formula is all one line - this board may break it into two) =IF(B1="Annual",A1*1,IF(B1="Monthly",A1*12,IF(B1=" 2xMonthly",A1*24,IF(B1="Bi-Weekly",A1*26,IF(B1="Weekly",A1*52,""))))) Hope that helps you some. As for learning about macros, here are some good 'get started' sources: Learning VBA there are a number of site around the net to help. http://www.mvps.org/dmcritchie/excel/getstarted.htm http://www.the-excel-advisor.com/exc...-tutorial.html http://class.et.byu.edu/ce270/vbaexcel_primer/intro.htm http://www.exceltip.com/excel_links.html there are other sites that provide usefull information about specific issues. http://www.contextures.com/ http://www.cpearson.com/ http://www.j-walk.com/ http://www.mcgimpsey.com/ http://www.rondebruin.nl/ http://www.mrexcel.com "JStiehl" wrote: I have never written a macro before and believe that using one might help my problem. People using the form I'm creating will enter their income in one cell and then choose one of the following from a drop down menu in another cell: yearly, monthly, twice per month, per 2 weeks, per week. I need to convert the income totals to the same format (yearly) before adding the numbers together in a new cell for a total (income totals will be multiplied by 12, 24, 26 or 52 depending on their choice from the drop down menu). There is the potential for 25 different incomes to be entered on this form. Any help you can provide is greatly appreciated! Thanks. |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
How to write a macro
Thanks so much for your help. The formula helped me convert the income in
one cell, and now I have to add the totals from potentially 25 different cells. I tried to use your formula to add the totals together, but I got an error message. Here is what I wrote: =IF(H14="yearly",G14*1,IF(H14="monthly",G14*12,IF( H14="twice per month",G14*24,IF(H14="bi-weekly",G14*26,IF(H14="weekly",G14*52,"")))))+IF(H 15="yearly",G15*1,IF(H15="monthly",G15*12,IF(H15=" twice per month",G15*24,IF(H15="bi-weekly",G15*26,IF(H15="weekly",G15*52,""))))) What am I doing wrong? I thought I would be able to add cell totals together the way I wrote it. I really appreciate your help. "JLatham" wrote: You should be able to do it without a macro if your "form" is a regular worksheet (which I'm assuming because you said entries are in cells). A1 has the income entry B1 has the list which has same entries as you'll see in this formula, which I put into C1 (remember that the formula is all one line - this board may break it into two) =IF(B1="Annual",A1*1,IF(B1="Monthly",A1*12,IF(B1=" 2xMonthly",A1*24,IF(B1="Bi-Weekly",A1*26,IF(B1="Weekly",A1*52,""))))) Hope that helps you some. As for learning about macros, here are some good 'get started' sources: Learning VBA there are a number of site around the net to help. http://www.mvps.org/dmcritchie/excel/getstarted.htm http://www.the-excel-advisor.com/exc...-tutorial.html http://class.et.byu.edu/ce270/vbaexcel_primer/intro.htm http://www.exceltip.com/excel_links.html there are other sites that provide usefull information about specific issues. http://www.contextures.com/ http://www.cpearson.com/ http://www.j-walk.com/ http://www.mcgimpsey.com/ http://www.rondebruin.nl/ http://www.mrexcel.com "JStiehl" wrote: I have never written a macro before and believe that using one might help my problem. People using the form I'm creating will enter their income in one cell and then choose one of the following from a drop down menu in another cell: yearly, monthly, twice per month, per 2 weeks, per week. I need to convert the income totals to the same format (yearly) before adding the numbers together in a new cell for a total (income totals will be multiplied by 12, 24, 26 or 52 depending on their choice from the drop down menu). There is the potential for 25 different incomes to be entered on this form. Any help you can provide is greatly appreciated! Thanks. |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
How to write a macro
Too many "IF"'s
You are limited to 7 nested IF's See Chip Pearson's site for workarounds to the limit. http://www.cpearson.com/excel/nested.htm Gord Dibben MS Excel MVP On Mon, 11 Aug 2008 04:36:00 -0700, JStiehl wrote: Thanks so much for your help. The formula helped me convert the income in one cell, and now I have to add the totals from potentially 25 different cells. I tried to use your formula to add the totals together, but I got an error message. Here is what I wrote: =IF(H14="yearly",G14*1,IF(H14="monthly",G14*12,IF (H14="twice per month",G14*24,IF(H14="bi-weekly",G14*26,IF(H14="weekly",G14*52,"")))))+IF(H 15="yearly",G15*1,IF(H15="monthly",G15*12,IF(H15=" twice per month",G15*24,IF(H15="bi-weekly",G15*26,IF(H15="weekly",G15*52,""))))) What am I doing wrong? I thought I would be able to add cell totals together the way I wrote it. I really appreciate your help. "JLatham" wrote: You should be able to do it without a macro if your "form" is a regular worksheet (which I'm assuming because you said entries are in cells). A1 has the income entry B1 has the list which has same entries as you'll see in this formula, which I put into C1 (remember that the formula is all one line - this board may break it into two) =IF(B1="Annual",A1*1,IF(B1="Monthly",A1*12,IF(B1=" 2xMonthly",A1*24,IF(B1="Bi-Weekly",A1*26,IF(B1="Weekly",A1*52,""))))) Hope that helps you some. As for learning about macros, here are some good 'get started' sources: Learning VBA there are a number of site around the net to help. http://www.mvps.org/dmcritchie/excel/getstarted.htm http://www.the-excel-advisor.com/exc...-tutorial.html http://class.et.byu.edu/ce270/vbaexcel_primer/intro.htm http://www.exceltip.com/excel_links.html there are other sites that provide usefull information about specific issues. http://www.contextures.com/ http://www.cpearson.com/ http://www.j-walk.com/ http://www.mcgimpsey.com/ http://www.rondebruin.nl/ http://www.mrexcel.com "JStiehl" wrote: I have never written a macro before and believe that using one might help my problem. People using the form I'm creating will enter their income in one cell and then choose one of the following from a drop down menu in another cell: yearly, monthly, twice per month, per 2 weeks, per week. I need to convert the income totals to the same format (yearly) before adding the numbers together in a new cell for a total (income totals will be multiplied by 12, 24, 26 or 52 depending on their choice from the drop down menu). There is the potential for 25 different incomes to be entered on this form. Any help you can provide is greatly appreciated! Thanks. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
how do i write a macro | Excel Worksheet Functions | |||
Help write Macro | Excel Discussion (Misc queries) | |||
How to write a macro?? | Excel Discussion (Misc queries) | |||
is it possible to execute write to the fields in another .xsl form a macro in another .xsl? e.g. some way to load another .xsl into an .xsl macro and write to its data? | Excel Worksheet Functions | |||
Q: how can I write this macro? | Excel Discussion (Misc queries) |