# Thread: Calculate mark up rates?

1. Registered User
Join Date
Nov 2003
Posts
300

## 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. Registered User
Join Date
Apr 2004
Location
Derbyshire, UK
Posts
805
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. Registered User
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. Registered User
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. Registered User
Join Date
Apr 2004
Location
Derbyshire, UK
Posts
805
That shouldn't be to difficult if you can define which row/cells you want it to apply to?

6. Registered User
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. Registered User
Join Date
Apr 2004
Location
Derbyshire, UK
Posts
805
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. www.gvee.co.uk
Join Date
Jan 2007
Location
UK
Posts
11,445

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*

9. Registered User
Join Date
Apr 2004
Location
Derbyshire, UK
Posts
805