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

Have you played Jackbox during the pandemic?

Discuss in The Lounge

Poll History