+ Reply to Thread
Results 1 to 6 of 6

Producing discount code in visual basic

  1. #1
    Registered User
    Join Date
    08-14-2009
    Location
    Biggin Hill, Kent
    MS-Off Ver
    Excel 2007
    Posts
    5

    Producing discount code in visual basic

    Hi,

    I am trying to write and function in Visual Basic that takes customer type and number of items purchased as inputs, and returns the correct level discount that should be given.

    The discount given is based on the type of customer and the amount of items purchased. For example:

    Customer1
    More than 50 items - 30%
    25 to 49 items - 20%
    15 to 24 items - 15%
    5 to 14 items - 10%
    Less than 5 items - 5%

    Customer2
    More than 25 items - 25%
    5 to 24 items - 15%
    Less than 5 items - 0%

    I have, so far, managed to produce code that takes the number of items purchased as an input, but I am unable to produce code that takes customer type as an input.

    Any help would be greatly appriecated.

    Thank you,

    Paul

  2. #2
    Forum Expert mrice's Avatar
    Join Date
    06-22-2004
    Location
    Surrey, England
    MS-Off Ver
    Excel 2013
    Posts
    4,964

    Re: Producing discount code in visual basic

    You could use a function like

    Please Login or Register  to view this content.
    Martin

    If my solution has saved you time and/or money, please consider sponsoring my run in the 2020 London Marathon in aid of Cancer Research UK.

    https://uk.virginmoneygiving.com/MartinRice

  3. #3
    Forum Guru shg's Avatar
    Join Date
    06-20-2007
    Location
    The Great State of Texas
    MS-Off Ver
    2003, 2010
    Posts
    40,646

    Re: Producing discount code in visual basic

    You could use a formula. If A1 contains customer (1 or 2), and B1 contains qty, then

    =CHOOSE(A1, LOOKUP(B1, {0,5,15,25,50},{0.05,0.1,0.15,0.2,0.3}), LOOKUP(B1, {0,5,25},{0,0.15,0.25}) )

    ... returns the discount.

    Or, as a UDF,
    Please Login or Register  to view this content.
    Last edited by shg; 08-15-2009 at 12:45 PM.
    Entia non sunt multiplicanda sine necessitate

  4. #4
    Registered User
    Join Date
    08-14-2009
    Location
    Biggin Hill, Kent
    MS-Off Ver
    Excel 2007
    Posts
    5

    Re: Producing discount code in visual basic

    Thank you for your responses.

    I have entered the code in Visual Basic and I am now trying to incorporate the function into my excel spreadsheet. However, I am having a few problems with producing a formula.

    I need to be able to enter either 'Customer1' or 'Customer2' into the 'Customer Type' cell and the number of items in the 'No. of Items' cell, then using the VB function return the correct level of discount in the 'Discount' cell.

    I am just not sure how best to go about this.

    I have attached a copy of my spreadsheet with the VB discount function.

    Thank you,

    Kind regards,

    Paul
    Attached Files Attached Files

  5. #5
    Forum Expert mrice's Avatar
    Join Date
    06-22-2004
    Location
    Surrey, England
    MS-Off Ver
    Excel 2013
    Posts
    4,964

    Re: Producing discount code in visual basic

    Try the attached.
    Attached Files Attached Files

  6. #6
    Registered User
    Join Date
    08-14-2009
    Location
    Biggin Hill, Kent
    MS-Off Ver
    Excel 2007
    Posts
    5

    Re: (Solved)Producing discount code in visual basic

    Thank you so much.

    This has really helped me out.

    Kind regards,

    Paul

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1