How to write a macro

[JStiehl]

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.

 

[Bob Phillips]

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)

 

[JLatham]

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/excel-macros-vba-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]

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(H15="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.

 

[Gord Dibben]

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