For an assignment I'm using VBA for an else..if function in excel. I'm trying to revise the column (commission) to be based off the column (sales). I tried to typing the function, but its not coming out right. Please help. My module:

Function rate(sales)

Dim commission_rate

If sales > 200000 Then

commission_rate = 0.1

ElseIf sales > 100000 Then

commission_rate = 0.05

ElseIf sales > 75000 Then

commission_rate = 0.04

ElseIf sales > 40000 Then

commission_rate = 0.03

ElseIf sales > 20000 Then

commission_rate = 0.01

ElseIf sales > 10000 Then

commission_rate = 0rate = commision_rate

End Function

re: " but it's not coming out right"Since we can't see your spreadsheet from where we're sitting, we don't know what you mean by "

but it's not coming out right".That said, the one thing I can tell you right up front is that there is a built-in Excel function RATE (it returns the interest rate per period of an annuity) therefore you can not create a User Defined Function with that name.

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.

Thanks I rewrote by vba to: Function commission (sales)

Dim commission_rate

If sales > 200000 Then

commission_rate = 0.1

ElseIf sales > 100000 Then

commission_rate = 0.05

ElseIf sales > 75000 Then

commission_rate = 0.04

ElseIf sales > 40000 Then

commission_rate = 0.03

ElseIf sales > 20000 Then

commission_rate = 0.01

ElseIf sales > 10000 Then

commission_rate = 0

End ifcommission = commision_rate

End Function--But when i type in the function "commission" referring to the "sales" column all i get is 0 even though i should be getting a percentage. Please help!

Too bad VBA doesn't have a spell checker. ;-) Look at your code very carefully.

In addition...what happens if "sales"

equalsone of your breakpoints?

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.

Well, VBA does have Option Explicit, and that'd catch the problem.

Ask Your Question

Weekly Poll