Code somewhat works. Please help?

[jsc3489]

The Value of b10 is always 0.20 no matter what I do. I'm so close I can taste
it, I think.
I need commission percentage (b10) to be chosen by profit percentage (b8)

This is what I have:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Parts
End Sub

Public Sub Parts()
If Range("b8").Value <= 0.04 Then Range("b10").Value = 0.05
If Range("b8").Value >= 0.05 <= 0.09 Then Range("b10").Value = 0.1
If Range("b8").Value >= 0.1 <= 0.14 Then Range("b10").Value = 0.13
If Range("b8").Value >= 0.015 <= 0.19 Then Range("b10").Value = 0.15
If Range("b8").Value >= 0.2 <= 0.24 Then Range("b10").Value = 0.17
If Range("b8").Value >= 0.25 <= 0.29 Then Range("b10").Value = 0.18
If Range("b8").Value >= 0.3 <= 0.34 Then Range("b10").Value = 0.19
If Range("b8").Value >= 0.35 <= 0.44 Then Range("b10").Value = 0.2
If Range("b8").Value >= 0.45 Then Range("b10").Value = 0.21
End Sub

 

[Niek Otten]

Change
If Range("b8").Value >= 0.05 <= 0.09 Then Range("b10").Value = 0.1
to
If Range("b8")>= 0.05 And Range ("b8") <= 0.09 Then....etc
BTW I recommend you reconsider the use of >= and <=; what if b8 is 0.095?

 

[Jim Thomlinson]

You can not use the criteria the way you have. it would have to be...

If Range("b8").Value >= 0.05 and Range("b8").Value<= 0.09 Then
Range("b10").Value = 0.1

But you will still run into an issue with vlaues like .095 whch will not be
captured. Do your code in the opposite order and use elseif statements should
work... Something like...

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Parts
End Sub

Public Sub Parts()
If Range("b8").Value >= 0.45 Then
Range("b10").Value = 0.21
elseIf Range("b8").Value >= 0.35 Then
Range("b10").Value = 0.2
elseIf Range("b8").Value >= 0.3 Then
Range("b10").Value = 0.19
elseIf Range("b8").Value >= 0.25 Then
Range("b10").Value = 0.18
elseIf Range("b8").Value >= 0.2 Then
Range("b10").Value = 0.17
elseIf Range("b8").Value >= 0.15 Then
Range("b10").Value = 0.15
elseIf Range("b8").Value >= 0.1 Then
Range("b10").Value = 0.13
elseIf Range("b8").Value >= 0.05 Then
Range("b10").Value = 0.1
else
Range("b10").Value = 0.05
endif
End Sub

 

[jsc3489]

SWEET!! Thank you so much.

 

[Jef Gorbach]

Using Select...Case should be faster and easier to read than multiple
IF..Thens and suggest storing the lookup in a variable so you only have one
line to update should the location of either B8 or B10 later change.

Public Sub Parts()
select case range("b8").value
case <= .04: answer=.05
case <= .09: answer=.1
case <= .14: answer=.13
etc
end select
range("b10").value = answer
End Sub