Results 1 to 9 of 9
  1. #1
    Join Date
    Nov 2003
    Posts
    300

    Question Unanswered: Calculate mark up rates?

    New to VBA ..

    Have a spreadsheet with 1 cell named INumber and other IRate..

    If the INumber is < 10, then I want the IRate to be 30
    If the INumber is < 50, then I want the IRate to be 25
    If the INumber is < 70, then I want the IRate to be 20

    then I can multiply the INumber and IRate and put the total in another cell...

    I think that a CASE is best way to handle this:

    Sub Markup()
    Select Case LNumber
    Case Is < 10
    LRate = 30
    Case Is < 50
    LRate = 25
    Case Is < 70
    LRate = 20
    Case Else
    LRate = 10
    End Select
    End Sub

    I tried to put in and run the code but really did nothing, so I am sure that I am missing more code.
    Any help is appreciated.

    Thanks!

  2. #2
    Join Date
    Apr 2004
    Location
    Derbyshire, UK
    Posts
    789
    Provided Answers: 1
    Hi

    not entirley sure what you are trying to do but may this will help
    Code:
    Sub Markup()
        Select Case Range("INumber")
            Case Is < 10
                Range("IRate") = 30
            Case Is < 50
                Range("IRate") = 25
            Case Is < 70
                Range("IRate") = 20
            Case Else
                Range("IRate") = 10
        End Select
    End Sub
    ??

    MTB

  3. #3
    Join Date
    Nov 2003
    Posts
    300
    Oh! I see what I was missing. This worked great when I went to the VBA code and ran it, the sheet updated.

  4. #4
    Join Date
    Nov 2003
    Posts
    300
    Follow-up question to the original. The above works for 1 row, what if I want to have 10 rows that do the exact same calculation? I still want to have the active-x control (button) that runs the code.

  5. #5
    Join Date
    Apr 2004
    Location
    Derbyshire, UK
    Posts
    789
    Provided Answers: 1
    That shouldn't be to difficult if you can define which row/cells you want it to apply to?

  6. #6
    Join Date
    Nov 2003
    Posts
    300
    I actually got it working by repeating the VBA code and renaming the variables to the corresponding cell names... INumber1, INumber2... etc. Just wondered if there was a more efficient way to program the same thing with less lines of VBA code?

    Thank you for your help MTB!

  7. #7
    Join Date
    Apr 2004
    Location
    Derbyshire, UK
    Posts
    789
    Provided Answers: 1
    Without seeing your code it is difficult to say if it can be simplified, but I susspect it can.
    Also, if the 10 row in quest are the 10 row below the first 'INumber' row and the IRate' cells are similaty related, then a simple loop using a counter and the cell/range offset method coud be used.

  8. #8
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Hi Mike - nice to see your face/username How are you doing nowadays?


    Just wanting to throw another tuppence in here... why are you doing this in VBA?
    It can be achieved with a formula:
    Code:
    =If(INumber<10, 30, If(INumber<50, 25, If(INumber<70, 20, 10)))
    *shrug*
    George
    Home | Blog

  9. #9
    Join Date
    Apr 2004
    Location
    Derbyshire, UK
    Posts
    789
    Provided Answers: 1
    Hi gvee

    I had had the same thought, but dadabasemon state quick he definitly he wanted to use code behine a button.

    Maybe I should have suggeted it anyway!

Posting Permissions

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