"IF THEN" macro help please

[DJM1216]

Hi there, I have never written a macro before and am looking for some help. I want to write an if then formula into a macro. Here is what I need to do (ficticious example)

I am comparing rates being charged to my company which is a function of a certain completion percent.

Say cell "C3" reads 80% (or any percent, can vary monthly)

I need to fill out the rate I am being charged by my vendor. For example pricing would look like this:
Vendor XYZ corp
50-75% $2.22
76-95% $1.95
96-100% $0.85


Now different vendors charge different rates, so I would need this formula for every vendor. Does that all go on one macro, or separate macros, that would be run by a "process" macro?

Basically, I need my final report to say what my completion rate is and because of that, what my vendors will charge me.

Does that make sense?

Thank you in advance.

 

[John McGraw]

On 2002-04-16 13:15, DJM1216 wrote:
Hi there, I have never written a macro before and am looking for some help. I want to write an if then formula into a macro. Here is what I need to do (ficticious example)
Thank you in advance.

Its a bit hard for me to understand exactly what you want to do. But if I am understanding correctly I think this is what you want to do...

First of all, this function can be used in a worksheet and recieves two arguments. The first is "Comp_Prcnt" which is the percentage completion. (you can use a reference like "a1" or wherever you keep the completion precentage) The second argument is the vendor code... You will need to asign each vendor with a code to which it will be refered. In the example XYZ corp is coded as 1, and ABC corp is 2.

I used a select case structure instead of if, then because it is alot easier in when you have alot of factors to work with. (You could do this with if-then but it would be very hard to read)<pre/>
Function Rate_Charged(Comp_Prcnt As Double, VendorCode As Integer)
'Declare constants to refer to the vendor codes
Const XYZcorp As Integer = 1
Const ABCcorp As Integer = 2

Select Case VendorCode
Case XYZcorp
Select Case Comp_Prcnt
Case 0.5 To 0.75
Rate_Charged = 2.22
Case 0.76 To 0.95
Rate_Charged = 1.95
Case 0.96 To 1
Rate_Charged = 0.85
End Select
Case ABCcorp
Select Case Comp_Prcnt
Case 0.5 To 0.75
Rate_Charged = 4.8
Case 0.76 To 0.95
Rate_Charged = 3.5
Case 0.96 To 1
Rate_Charged = 2.22
End Select
End Select

End Function</pre>


Just enter this function into a formula and it will return the rate charged by the vendor.

You can adjust the code to add more vendors with their respective codes. Just add a new case in the<font size =-1/>Select Case VendorCode</font size =+1> section of the code.
This message was edited by John McGraw on 2002-04-16 15:06

 

[DJM1216]

Where you're putting in "Vendorcode" is that the column heading, or is that where I put in the cell reference?

I entered this macro, but no results.

Also when I start a new macro it starts with something like "Sub Macro ()", what is that for?

Thanks.

 

[John McGraw]

On 2002-04-17 13:22, DJM1216 wrote:
Where you're putting in "Vendorcode" is that the column heading, or is that where I put in the cell reference?

I entered this macro, but no results.

Also when I start a new macro it starts with something like "Sub Macro ()", what is that for?

Thanks.

Ok, you need to paste this function into a module. Do this:

Press ALT+F11
Choose the "Insert" Menu
Choose "Module"

Now an empty module will pop up. Select the code I wrote for you in the above message and copy it, then paste it into the new VBA module.

In excel you can now enter this function just like you would any other function. Do the following as a test:

In cell A1 enter: "80%"
in cell B1 enter: "=Rate_Charged(A1,1)"

In the formula "=Rate_Charged(a1,1)", the first argument is the %completion or A1, the second argument is the vendor code. (1 in this example, or XYZcorp) If you would have entered: "=Rate_Charged(a1,2)" that would have given you the rate charged by ABC corp.

It seems you are very new to VBA. Check out the following to get some basic understanding http://www.mrexcel.com/tip038.shtml


Also for more articles on excel and VBA try:

http://www.mrexcel.com/index.html#past

Especialy check out the sections "VBA Macros - Introduction" and "VBA Macros - Examples"

I hope that helps. If you still cant figure it out, let me know. Dont try to record a macro, you just need to create a new module and dump the code into it.
This message was edited by John McGraw on 2002-04-18 12:23