Ordering in pallet quantities - overriding an entered value in the same cell

[Phantom645]

Hi,

I have staff that place purchase orders with our parent company. I would like them to order by specific pallet quantity - lots of 105 cartons, 99 cartons...etc.

So if user enters the number of cartons they would like to order (say 100) into cell B1, but this particular product comes in pallets of 105, I need the worksheet to automatically roundup the entered value to 105 and override the original entered value in the same cell. Conversely, if the number entered is 116, I need the worksheet to round down to 105.

Example:
User enters "100" in cell B1. System rounds value to nearest 105 and overrides initial entered value. Thus cell B1 now displays 105.
User enters "540" in cell B1. System rounds value to nearest 105 and overrides initial entered value. Thus cell B1 now displays 525.

I need to apply to 13 products, thus 13 cells.

Thanks Gurus!

Regards,

Paul

 

[jasonb75]

You could use data validation to force the user to enter a correct factor.

=MOD(B2,VLOOKUP(A2,List,2,0))=0

Where B2 is the cell with the quantity to validate. A2 is the name of the product to validate. List refers to a 2 column table with product names in the left column and correct buying quantities in the right column.

 

[Phantom645]

Thanks Jason, that could definitely work but I would prefer the automatic override function.

 

[Tricepsratops]

Maybe this can help you

Nuevo Hoja de cálculo de Microsoft Excel.xlsm
	A	B
1		Orders
2		105
3		0
4		0
5		105
6		205
7
8
9
10
Sheet1

Sub Worksheet_Change(ByVal Target As Range)

Dim CELL As Range

For Each CELL In Range("B2:B14")

If (CELL.Value > 0) And (CELL.Value < 105) Then
    CELL.Value = 105
   
ElseIf (CELL.Value > 105) And (CELL.Value < 205) Then
    CELL.Value = 205
      
End If

Next CELL
End Sub

This code checks for the values in B2:B14, if the number is >0 and <105 then puts 105, same for 205 ... you can make more scenarios.

EDIT: you have to paste the code into the desired worksheet not inside a module.

 

[jasonb75]

Simplifying the vba approach, this will round a number entered into column B based on a description in column A being compared to a list in G2:H100.
I've tested it in the table below using a quantity of 500 for each item, the item not found in the list is unchanged.

Book1
	A	B				F	G	H
1							Item	Pack
2	Bananas	492					Apples	99
3	Strawberries	500					Oranges	105
4	Apples	495					Bananas	123
5	Oranges	525
Sheet2

Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)
Dim c As Range
Application.EnableEvents = False
If Not Intersect(Target, Range("B:B")) Is Nothing Then
    For Each c In Intersect(Target, Range("B:B"))
        If IsNumeric(c.Value) Then
            c.Value = Evaluate("=IFERROR(MROUND(" & c.Value & ",VLOOKUP(""" & c.Offset(, -1).Value & """,$G$2:$H$100,2,0))," & c.Value & ")")
        End If
    Next
End If
Application.EnableEvents = True
End Sub

 

[Fluff]

How about

Private Sub Worksheet_Change(ByVal Target As Range)
   If Target.CountLarge > 1 Then Exit Sub
   If Not Intersect(Target, Range("B1:B13")) Is Nothing Then
      Application.EnableEvents = False
      Target.Value = Application.MRound(Target.Value, 105)
      Application.EnableEvents = True
   End If
End Sub

 

[Phantom645]

Simplifying the vba approach, this will round a number entered into column B based on a description in column A being compared to a list in G2:H100.
I've tested it in the table below using a quantity of 500 for each item, the item not found in the list is unchanged.

Book1
	A	B				F	G	H
1							Item	Pack
2	Bananas	492					Apples	99
3	Strawberries	500					Oranges	105
4	Apples	495					Bananas	123
5	Oranges	525
Sheet2

Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)
Dim c As Range
Application.EnableEvents = False
If Not Intersect(Target, Range("B:B")) Is Nothing Then
    For Each c In Intersect(Target, Range("B:B"))
        If IsNumeric(c.Value) Then
            c.Value = Evaluate("=IFERROR(MROUND(" & c.Value & ",VLOOKUP(""" & c.Offset(, -1).Value & """,$G$2:$H$100,2,0))," & c.Value & ")")
        End If
    Next
End If
Application.EnableEvents = True
End Sub

This worked perfectly thank you!

 

[Phantom645]

Simplifying the vba approach, this will round a number entered into column B based on a description in column A being compared to a list in G2:H100.
I've tested it in the table below using a quantity of 500 for each item, the item not found in the list is unchanged.

Book1
	A	B				F	G	H
1							Item	Pack
2	Bananas	492					Apples	99
3	Strawberries	500					Oranges	105
4	Apples	495					Bananas	123
5	Oranges	525
Sheet2

Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)
Dim c As Range
Application.EnableEvents = False
If Not Intersect(Target, Range("B:B")) Is Nothing Then
    For Each c In Intersect(Target, Range("B:B"))
        If IsNumeric(c.Value) Then
            c.Value = Evaluate("=IFERROR(MROUND(" & c.Value & ",VLOOKUP(""" & c.Offset(, -1).Value & """,$G$2:$H$100,2,0))," & c.Value & ")")
        End If
    Next
End If
Application.EnableEvents = True
End Sub

Jason, thanks again for the help so far. If I want to extend this code to a full row what changes do I need to make to the code? Looking at your code it uses offset to reference.

So in the above example, Cell A7 is the product code, Row 7 is where my staff enter the quantities for each week's order of that product.

The reference table with the data for the vlookup is 'container calc'!$A$61:$B$73

I assume that I have to repeat the code for each row?

Thanks again!

 

[jasonb75]

No repetition needed, I've removed the column restriction from the code and replaced the offset with a reference to column A.

Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)
Dim c As Range
If Target.Row < 7 Then Exit Sub
Application.EnableEvents = False
    For Each c In Target
        If IsNumeric(c.Value) Then
            c.Value = Evaluate("=IFERROR(MROUND(" & c.Value & ",VLOOKUP(""" & Cells(1, c.Row).Value & """,'container calc'!$A$61:$B$73 ,2,0))," & c.Value & ")")
        End If
    Next
Application.EnableEvents = True
End Sub