Thanks Thanks:  0
Likes Likes:  0
Results 1 to 4 of 4

Thread: "IF THEN" macro help please

  1. #1
    Board Regular
    Join Date
    Apr 2002
    Location
    Delray Beach, FL
    Posts
    102
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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.

  2. #2
    Board Regular
    Join Date
    Feb 2002
    Posts
    76
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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)


    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




    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 Select Case VendorCode section of the code.





    [ This Message was edited by: John McGraw on 2002-04-16 15:06 ]

  3. #3
    Board Regular
    Join Date
    Apr 2002
    Location
    Delray Beach, FL
    Posts
    102
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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.

  4. #4
    Board Regular
    Join Date
    Feb 2002
    Posts
    76
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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 ]

Some videos you may like

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •