I am trying to do an if statement, involving a commission shedule,
with the following data, on VBA:

STOCK PRICE COMMISSION
$0 - $.24 2.5%
$.25 - $1 $35 + $.005 per share
$1.01 - $2 $35 + $.02 per share
$2.01 - $5 $35 + $.03 per share
$5.01 - $10 $35 + $.04 per share
$10.01- $20 $35 + $.05 per share
$20.01 and over $35 + $.06 per share

I have set up the cells on my spread sheet as follows:

A1 = quantity (no. of shares)
A2 = price
A3 = contains the answer / commission.

Sub Commission()
If [a2] 0 And [a2] <= 0.24 Then
[a3] = [a1] * [a2] * 0.025
ElseIf [a2] = 0.25 And [a2] <= 1 Then
[a3] = [a1] * 0.005 + 35
ElseIf [a2] = 1.01 And [a2] <= 2 Then
[a3] = [a1] * 0.02 + 35
ElseIf [a2] = 2.01 And [a2] <= 5 Then
[c1] = [a1] * 0.03 + 35
ElseIf [a2] = 5.01 And [a2] <= 10 Then
[c1] = [a1] * 0.04 + 35
ElseIf [a2] = 10.01 And [a2] <= 20 Then
[c1] = [a1] * 0.05 + 35
ElseIf [a2] = 20.01 Then
[c1] = [a1] * 0.06 + 35
Else: [c1] = ""

End Sub

I have manually typed the above exactly as shown (nothing else)
however it does not work. When for example I have cell A1 = 500;
A2 = 5.25; instead of cell A3 showing the answer $55. it is blank?

Your help in this mater is greatly appreciated.

Susan

You didn't have an end if
You changed to C1 about half way through your routine.

Sub Commission()
If [a2] 0 And [a2] <= 0.24 Then
[a3] = [a1] * [a2] * 0.025
ElseIf [a2] = 0.25 And [a2] <= 1 Then
[a3] = [a1] * 0.005 + 35
ElseIf [a2] = 1.01 And [a2] <= 2 Then
[a3] = [a1] * 0.02 + 35
ElseIf [a2] = 2.01 And [a2] <= 5 Then
[a3] = [a1] * 0.03 + 35
ElseIf [a2] = 5.01 And [a2] <= 10 Then
[a3] = [a1] * 0.04 + 35
ElseIf [a2] = 10.01 And [a2] <= 20 Then
[a3] = [a1] * 0.05 + 35
ElseIf [a2] = 20.01 Then
[a3] = [a1] * 0.06 + 35
Else
[a3] = ""
End If
End Sub

--
Regards,
Tom Ogilvy

<Susan Hayes wrote in message
...
I am trying to do an if statement, involving a commission shedule,
with the following data, on VBA:

STOCK PRICE COMMISSION
$0 - $.24 2.5%
$.25 - $1 $35 + $.005 per share
$1.01 - $2 $35 + $.02 per share
$2.01 - $5 $35 + $.03 per share
$5.01 - $10 $35 + $.04 per share
$10.01- $20 $35 + $.05 per share
$20.01 and over $35 + $.06 per share

I have set up the cells on my spread sheet as follows:

A1 = quantity (no. of shares)
A2 = price
A3 = contains the answer / commission.

Sub Commission()
If [a2] 0 And [a2] <= 0.24 Then
[a3] = [a1] * [a2] * 0.025
ElseIf [a2] = 0.25 And [a2] <= 1 Then
[a3] = [a1] * 0.005 + 35
ElseIf [a2] = 1.01 And [a2] <= 2 Then
[a3] = [a1] * 0.02 + 35
ElseIf [a2] = 2.01 And [a2] <= 5 Then
[c1] = [a1] * 0.03 + 35
ElseIf [a2] = 5.01 And [a2] <= 10 Then
[c1] = [a1] * 0.04 + 35
ElseIf [a2] = 10.01 And [a2] <= 20 Then
[c1] = [a1] * 0.05 + 35
ElseIf [a2] = 20.01 Then
[c1] = [a1] * 0.06 + 35
Else: [c1] = ""

End Sub

I have manually typed the above exactly as shown (nothing else)
however it does not work. When for example I have cell A1 = 500;
A2 = 5.25; instead of cell A3 showing the answer $55. it is blank?

Your help in this mater is greatly appreciated.

Susan

It appears that afew C1's have found their way into your code...

ElseIf [a2] = 2.01 And [a2] <= 5 Then
[c1] = [a1] * 0.03 + 35
ElseIf [a2] = 5.01 And [a2] <= 10 Then
[c1] = [a1] * 0.04 + 35
ElseIf [a2] = 10.01 And [a2] <= 20 Then
[c1] = [a1] * 0.05 + 35
ElseIf [a2] = 20.01 Then
[c1] = [a1] * 0.06 + 35
Else: [c1] = ""

Could that be where it's going wrong?


<Susan Hayes wrote in message
...
I am trying to do an if statement, involving a commission shedule,
with the following data, on VBA:

STOCK PRICE COMMISSION
$0 - $.24 2.5%
$.25 - $1 $35 + $.005 per share
$1.01 - $2 $35 + $.02 per share
$2.01 - $5 $35 + $.03 per share
$5.01 - $10 $35 + $.04 per share
$10.01- $20 $35 + $.05 per share
$20.01 and over $35 + $.06 per share

I have set up the cells on my spread sheet as follows:

A1 = quantity (no. of shares)
A2 = price
A3 = contains the answer / commission.

Sub Commission()
If [a2] 0 And [a2] <= 0.24 Then
[a3] = [a1] * [a2] * 0.025
ElseIf [a2] = 0.25 And [a2] <= 1 Then
[a3] = [a1] * 0.005 + 35
ElseIf [a2] = 1.01 And [a2] <= 2 Then
[a3] = [a1] * 0.02 + 35
ElseIf [a2] = 2.01 And [a2] <= 5 Then
[c1] = [a1] * 0.03 + 35
ElseIf [a2] = 5.01 And [a2] <= 10 Then
[c1] = [a1] * 0.04 + 35
ElseIf [a2] = 10.01 And [a2] <= 20 Then
[c1] = [a1] * 0.05 + 35
ElseIf [a2] = 20.01 Then
[c1] = [a1] * 0.06 + 35
Else: [c1] = ""

End Sub

I have manually typed the above exactly as shown (nothing else)
however it does not work. When for example I have cell A1 = 500;
A2 = 5.25; instead of cell A3 showing the answer $55. it is blank?

Your help in this mater is greatly appreciated.

Susan