Range Formula

[Hamed parhizkar]

Please look below, I did all of this but in the cell it comes up with an error
#NAME?

What do I do?


Use a VBA function like the one below
call with
=bonus(b26,b27)

Function bonus(budget, actual)

Select Case (actual - budget)

Case -15 To -11
bonus = 1000
Case -10 To -4
bonus = 1500
Case -5 To -1
bonus = 1750
Case 0 To 4
bonus = 2000
Case 5 To 9
bonus = 2250
Case 10 To 14
bonus = 2500
Case 15 To 19
bonus = 3000
Case 20 To 24
bonus = 3250
Case 25 To 29
bonus = 3500
Case 30 To 34
bonus = 4000
Case 35 To 1000
bonus = 4500
End Select


End Function

 

[Joel]

1) Make sure the code is in a VBA module (not this workbook or one of the
sheets). Al
2) Does other macros run in the workbook? Make sure security level is set
to medium or low.

The code is working for me.

 

[Hamed parhizkar]

In the cell i put =bonus(b26,b27)

I opened up the control toolbox and clicked on view code then I copied and
pasted this:

Function bonus(budget, actual)

Select Case (actual - budget)

Case -15 To -11
bonus = 1000
Case -10 To -4
bonus = 1500
Case -5 To -1
bonus = 1750
Case 0 To 4
bonus = 2000
Case 5 To 9
bonus = 2250
Case 10 To 14
bonus = 2500
Case 15 To 19
bonus = 3000
Case 20 To 24
bonus = 3250
Case 25 To 29
bonus = 3500
Case 30 To 34
bonus = 4000
Case 35 To 1000
bonus = 4500
End Select


End Function

And all it says in the cell is #NAME?

 

[Joel]

You need to move the code from the worksheet to a modume. In the VBA window
menu Insert - Module. Add code here. If you look at the project window in
VBA you wiioll see sheets, thisworkbook, and modules. This function has to
be in a module sheet. You will have to insert a new module. In a new
workbook there are no modules until you create one.

 

[Hamed parhizkar]

I did this but now I get a vba error that says compile error and the cursor
goes right before "actual" up top after the budget,

???

 

[Joel]

Make sure you have all the code and nothing extra. The macro should start
with the word "Function" and go to the "End Function" statements. There
should not be any of the > that appears on the website. Use the original
code I sent that only contains the text and nothing that was posted by the
website

Also make sure you remove the code from where you first put the code.

 

[Hamed parhizkar]

Is there anyway I could send you just that one sheet, it still wont work, do
you have an email???

 

[Joel]

Here is the code again. I get no errors.




Function bonus(budget, actual)

Select Case (actual - budget)

Case -15 To -11
bonus = 1000
Case -10 To -4
bonus = 1500
Case -5 To -1
bonus = 1750
Case 0 To 4
bonus = 2000
Case 5 To 9
bonus = 2250
Case 10 To 14
bonus = 2500
Case 15 To 19
bonus = 3000
Case 20 To 24
bonus = 3250
Case 25 To 29
bonus = 3500
Case 30 To 34
bonus = 4000
Case 35 To 1000
bonus = 4500
End Select
End Function

 

[Hamed parhizkar]

I understand the vba, everything you have in there i have under a module,
what do you have in the actual cell though?

 

[Joel]

A VBA function looks just like a standard excel function to the worksheet.

=bonus(b26,b27)

where b26 is budget and b27 is actual

 

[Hamed parhizkar]

It works, I was putting in =bonus(budget,actual)... And thats why you are the
excel genius

 

[Hamed parhizkar]

I tried to make up a new module for used cars

-14 to -8 750
-7 to -1 1000
0 to +6 1250
+7 to +13 1750
+14 and over 2250

I took pretty much what you had and changed it, and changed the function to
used bonus but all sorts of errors come up and it also interferes with the
previous bonus function we had, how do I fix this?