Calculating desired profit margin depending on our cost

[calpolymis]

Hi, I need to develope a macro for Excel to calculate desired profit margin depending on our cost. Here's the scenario.

A1: landing cost

If 0 <= A1 <= 2, then multiply 200% to A1 and write the target profit margin on B1
If 3 <= A1 <= 5, then multiply 150% to A1 and write the target profit margin on B1
If 6 <= A1 <= 10, then multiply 75% to A1 and write the target profit margin on B1
.....

So, it's a CASE scenario that determins the target profit margin depending on the cost group. I've been pretty much away from this kind of stuff so I really need someone's help to solve this problem.

I would really appreciate some detailed instructions to implement on Excel.

Please let me know if you need any further detail.

Thank you.

 

[Andrzejek]

So what have you tried so far and where are you stuck?

Have fun.

---- Andy

 

[calpolymis]

Hi, Andy. Actually, I'm not quite familiar with this (even though I worked in some vba codes before.)

I don't know if this could be achieved by a funtion like VLOOKUP maybe? Or I need to develope macro vba. So, basically I need direction at this point, even before detail, even though I want to complete this by tomorrow.

Thanks.

 

[calpolymis]

Or below is my logic to achieve this. I found this sample code by googling and modified it.

MyCaseFuntion()

Select Case Range("A1").Value

Case ( if value in A1 is greater than 0 and less than 2)

Range("B1").Value = 200

Case ( if value in A1 is greater than 2 and less than 5)

Range("B1").Value = 150

Case ( if value in A1 is greater than and less than 10 )

Range("B1").Value = 100

Case ( if it's over 10 )

Range("B1").Value = 20

End Select

And I have 500 items to apply this calculation. Thanks.

 

[calpolymis]

I need some help how to write the correct vba function (if vba funtion is the way to go,) or if there's another way, I need some advice. Also, how to apply this funtion to my 500 items, not to just one item. Thanks.

 

[SkipVought]

Hi,

Use right on the sheet in column B...

Function MyCaseFuntion(a)

  Select Case a
    Case Is < 0
    Case Is < 2: MyCaseFunction = 200
    Case Is < 5: MyCaseFunction = 150
    Case Is < 10: MyCaseFunction = 100
    Case Else: MyCaseFunction = 20
  End Select

End Function

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[Andrzejek]

Or something simple like this (Modify as needed):

Sub Macro1()
Dim i As Integer

For i = 1 To 500
    Select Case Range("A" & i).Value
        Case Is < 2
            Range("B" & i).Value = Range("A" & i).Value * 2
        Case Is < 5
            Range("B" & i).Value = Range("A" & i).Value * 1.5
        Case Is < 10
            Range("B" & i).Value = Range("A" & i).Value * 0.75
        Case Else
            '???
    End Select
Next i

End Sub

Have fun.

---- Andy