| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Code somewhat works. Please help?
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 -- I reject your reality and substitute my own. Promote hydrogen - one of the best "clean" fuels there are! |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Code somewhat works. Please help?
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? -- Kind regards, Niek Otten "jsc3489" wrote in message ... 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 -- I reject your reality and substitute my own. Promote hydrogen - one of the best "clean" fuels there are! |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Code somewhat works. Please help?
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 -- HTH... Jim Thomlinson "jsc3489" wrote: 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 -- I reject your reality and substitute my own. Promote hydrogen - one of the best "clean" fuels there are! |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Code somewhat works. Please help?
SWEET!! Thank you so much.
-- I reject your reality and substitute my own. Promote hydrogen - one of the best "clean" fuels there are! "Jim Thomlinson" wrote: 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 -- HTH... Jim Thomlinson "jsc3489" wrote: 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 -- I reject your reality and substitute my own. Promote hydrogen - one of the best "clean" fuels there are! |
|
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Code somewhat works. Please help?
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 "jsc3489" wrote in message ... 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 -- I reject your reality and substitute my own. Promote hydrogen - one of the best "clean" fuels there are! |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Why this code works and other do not? Newbie here. | Excel Programming | |||
| Code works for any WBK except the PMW | Excel Programming | |||
| Half of the code works? | Excel Programming | |||
| Why won't this code works | Excel Programming | |||
| VBA Code works in 2000 not 97 | Excel Programming | |||
Linear Mode
