IF THEN question

[wvarner]

Customer will select from a drop down list in B16. In E16 they can type in a size for that product that was specified in B16 but there is a minimum size for each product. I want to automaticallly bump that number up to the minimum required.

For example, if they choose Duvets or Shams or Drapes in B16, the minimum size they can specify is 1. So if they type in 0.5, I want to bump that number up to 1 automatically.

If they choose Comforter or Comforter Sets, the minimum size they can specify is 3. So if they type in 2, I want to bump that number up to 3 automatically.

I don't want #VALUE! to show in the blank field.

Help?

 

[stnkynts]

Put in Worksheet Change event. You can add as many options as you want just follow the template. If you have a bunch to do it may be easier to shorten their names to say....minCom, minDr, etc

Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)
Dim minDuvets As Integer, minShams As Integer
Dim minDrapes As Integer, minComforters As Integer
'Dim "a new defined name" as Integer
 
minDuvets = 2
minShams = 4
minDrapes = 1
minComforters = 3
'any other defined name with minimum value
 
If Target.Address = "$E$16" Then
    Select Case Target.Offset(0, -3).Value
        Case Is = "Duvets"
            If Target.Value < minDuvets Then
                Target.Value = minDuvets
            End If
        Case Is = "Shams"
            If Target.Value < minShams Then
                Target.Value = minShams
            End If
        Case Is = "Drapes"
            If Target.Value < minDrapes Then
                Target.Value = minDrapes
            End If
        Case Is = "Comforters"
            If Target.Value < minComforters Then
                Target.Value = minComforters
            End If
        'Case Is = "whatever you want to call it"
            'If Target.Value < defined name from above Then
             '   Target.Value = defined name from above
            'End If
    End Select
End If
 
End Sub

 

[wvarner]

Thank you!

OK so I used the size instead of the product to make it easier. Here is what I came up with:

Private Sub Worksheet_Change(ByVal Target As Range)
Dim min23X23X As Integer, min19X15X As Integer
Dim min13X10X As Integer, min11X9X As Integer
Dim min9X11X As Integer
min23X23X = 3
min19X15X = 3
min13X10X = 1
min11X9X = 1
min9X11X = 1

If Target.Address = "$E$16" Then
Select Case Target.Offset(0, -3).Value
Case Is = "23X23X"
If Target.Value < min23X23X Then
Target.Value = min23X23X
End If
Case Is = "19X15X"
If Target.Value < min19X15X Then
Target.Value = min19X15X
End If
Case Is = "13X10X"
If Target.Value < min13X10X Then
Target.Value = min13X10X
End If
Case Is = "11X9X"
If Target.Value < min11X9X Then
Target.Value = min11X9X
End If
Case Is = "9X11X"
If Target.Value < min9X11X Then
Target.Value = min9X11X
End If
End Select
End If

End Sub

What is happening is that it is rounding the number either down or up to the nearest whole number.

For example, when I type in 0.5 it bumps it up to 1. If I type in 1.5 it bumps up to 2. If I type in 2.25 it bumps down to 2. If I type in 2.5 it bumps up to 3.

For each size there is a minimum. So if they type in a number that is less than the minimum, I want to bump it up to the minimum. But I want them to be able to type in any number freely over the minimum.

For example, if the minimum is 3, I want them to be able to type in 3.756 or 4.25 or 100.276. The sky is the limit.

I appreciate your help.

 

[stnkynts]

Format cell E16 to be a number with 3 decimal places. Should fix your problem.

 

[wvarner]

That didn't work.

Is my code correct?

If it's easier, my AOL instant message ID is WendyImex. I will be available on AOL until 5:00 EST Thank you!

 

[stnkynts]

Hmmm. I tried it on a worksheet exactly as described and it worked perfectly fine for me. I could, for example, select "23x23x" from the drop down list in cell B16 and enter 3.15 into cell E16 and it would hold it exactly at that. If i changed E16 to 2.93 it would automatically change it to 3.00.

 

[wvarner]

Hmmmm....that is puzzling. Do you think it has anything to do with this part of the code?:

Select Case Target.Offset(0, -3).Value

Maybe I should start all over with the code.

I feel that I am almost there...

 

[wvarner]

Are you still willing to help me?