Run Time Error "11" - Division by Zero

[olatzcelaya]

hi all,

I am doing a program in VBA. When I run the program the error shown in the subject appears. I can not find where the error is. If anybody can help I will appreciate it a lot.;-)

The code, where the error appears, is the following one (the variables have been defined previously):

Dim myNumberStacks As Integer
Dim myPalletWidth1 As Double
Dim myPalletLength1 As Double
Dim myTolerance As Integer
Dim myQuantity As Integer
Dim mySheetWidth As Double
Dim mySheetLength As Double
Dim myFluteWeight As Double
Dim myFluteThickness As Double
Dim myOverhang As Integer
Dim myPaperWeight As Double
Dim myLorryHeight As Integer
Dim myPalletHeight As Integer
Dim myNumberSheetsPalletWeight As Integer
Dim myMaterialHeight As Double
Dim myNumberSheetsPalletHeight As Integer
Dim myNumberSheetsPallet_1 As Integer
Dim myNumberSheetsStack_1 As Integer
Dim myNumberSheetsStack_A As Integer
Dim myNumberSheetsPallet_A As Integer
Dim myNumberSheetsStack_B As Integer
Dim myNumberSheetsPallet_B As Integer
Dim myOverallNumberPallets As Integer
Dim myFullPallets As Integer
Dim mySheetsRedistribute As Integer
Dim myNumberStacks_1 As Integer
Dim myNumberSheetsStack_2 As Integer
Dim myNumberSheetsPallet_2 As Integer
Dim myNumberStacks_2 As Integer
Dim myNumberSheetsLeftPartPallet As Integer
Dim myMaximumAddingSheetsPartPallet As Integer
Dim myNumberSheetsPartPallets As Integer
Dim Pallet_Width2(24) As Integer
Dim Pallet_Length2(24) As Integer
Dim myNumberPallets As Integer
Dim myPalletWidth As Integer
Dim myPalletLength As Integer
Dim i As Integer



Private Sub CommandButton8_Click()

If CommandButton12.Enabled = False Then
'The customer does not require a specific number of sheets per stack

If ComboBox7.Text = "F" Then
CommandButton15.Enabled = False
' The number of sheets per stack2 will be enabled when we redistribute the sheets of the last part pallet onto the other pallets. This button will be enabled when we have a "B", "E", "EB", "EE", or "NE" flutes. And when the overall quantity of sheets in the last part pallet is less than the half of the maximum quantity that can be loaded per pallet

CommandButton16.Enabled = False
' The number of sheets per pallet2 will be enabled when we redistribute the sheets of the last part pallet onto the other pallets. This button will be enabled when we have a "B", "E", "EB", "EE", or "NE" flutes. And when the overall quantity of sheets in the last part pallet is less than the half of the maximum quantity that can be loaded per pallet

Sheet1.Cells(25, 22) = NO
' If there are cardboard sheets of "F" or "N" flute, the sheets in the last part pallet are not redistribute onto the other pallets. Even if the number of sheets in the last part pallet are less than the half of the maximum quantity. The reason of this is because they are very delicated flutes

If mySheetWidth <= 1200 And mySheetLength <= 1200 Then
myNumberSheetsStack_1 = 900
myNumberSheetsPallet_1 = 900
' If we have "N" or "F" flutes the number of stacks per pallet is always 1

ElseIf mySheetWidth > 1200 And mySheetLength > 1200 Then
myNumberSheetsStack_1 = 700
myNumberSheetsPallet_1 = 700
End If

ElseIf ComboBox7.Text = "N" Then
CommandButton15.Enabled = False
' The number of sheets per stack2 will be enabled when we redistribute the sheets of the last part pallet onto the other pallets. This button will be enabled when we have a "B", "E", "EB", "EE", or "NE" flutes. And when the overall quantity of sheets in the last part pallet is less than the half of the maximum quantity that can be loaded per pallet

CommandButton16.Enabled = False
' The number of sheets per pallet2 will be enabled when we redistribute the sheets of the last part pallet onto the other pallets. This button will be enabled when we have a "B", "E", "EB", "EE", or "NE" flutes. And when the overall quantity of sheets in the last part pallet is less than the half of the maximum quantity that can be loaded per pallet

Sheet1.Cells(25, 22) = NO
' If there are cardboard sheets of "F" or "N" flute, the sheets in the last part pallet are not redistribute onto the other pallets. Even if the number of sheets in the last part pallet are less than the half of the maximum quantity. The reason of this is because they are very delicated flutes

If mySheetWidth <= 1200 And mySheetLength <= 1200 Then
myNumberSheetsStack_1 = 1200
myNumberSheetsPallet_1 = 1200

ElseIf mySheetWidth > 1200 And mySheetLength > 1200 Then
myNumberSheetsStack_1 = 1000
myNumberSheetsPallet_1 = 1000
End If
' In these if's we have defined the number of sheets per stacks and per pallet for "N" and "F" flute

ElseIf ComboBox7.Text = "B" Or ComboBox7.Text = "E" Or ComboBox7.Text = "EB" Or ComboBox7.Text = "EE" Or ComboBox7.Text = "NE" Then
myMaterialHeight = (myLorryHeight - 170 - (2 * myPalletHeight)) / 2
myNumberSheetsPalletHeight = (myMaterialHeight \ myFluteThickness) * myNumberStacks
myNumberSheetsPalletWeight = 750000 \ myPaperWeight

If myNumberSheetsPalletWeight <= myNumberSheetsPalletHeight Then
myNumberSheetsStack_A = myNumberSheetsPalletWeight \ myNumberStacks
myNumberSheetsPallet_A = myNumberSheetsStack_A * myNumberStacks

ElseIf myNumberSheetsPalletWeight > myNumberSheetsPalletHeight Then
myNumberSheetsStack_A = myMaterialHeight \ myFluteThickness
myNumberSheetsPallet_A = (myMaterialHeight \ myFluteThickness) * myNumberStacks
End If
End If
' In these last if's we decide the number of sheets per stack

ElseIf CommandButton12.Enabled = True Then
myNumberSheetsStack_B = Sheet1.Cells(5, 10)
myNumberSheetsPallet_B = myNumberSheetsStack_B * myNumberStacks
myMaterialHeight = (myLorryHeight - 170 - 2 * myPalletHeight) / 2
myNumberSheetsPalletHeight = (myMaterialHeight \ myFluteThickness) * myNumberStacks
myNumberSheetsPalletWeight = 750000 \ myPaperWeight

If myNumberSheetsPalletWeight <= myNumberSheetsPalletHeight Then
Do While myNumberSheetsPallet_B > myNumberSheetsPalletWeight
MsgBox "Introduce a new quantity of number of sheets per stack"
myNumberSheetsPallet_1 = myNumberSheetsStack_B * myNumberStacks
myNumberSheetsStack_1 = myNumberSheetsStack_B
Loop

ElseIf myNumberSheetsPalletWeight > myNumberSheetsPalletHeight Then
Do While myNumberSheetsPallet_B > myNumberSheetsPalletHeight
MsgBox "Introduce a new quantity of number of sheets per stack"
myNumberSheetsPallet_1 = myNumberSheetsStack_B * myNumberStacks
myNumberSheetsStack_1 = myNumberSheetsStack_B
Loop
End If
' In these last if's it is checked that the customer does not exceed the maximum number of sheets per stack

End If
'In the following steps we will checked if there are any part pallets

If ComboBox7.Text = "B" Or ComboBox7.Text = "E" Or ComboBox7.Text = "EB" Or ComboBox7.Text = "EE" Or ComboBox7.Text = "NE" Then

If (myQuantity Mod myNumberSheetsPallet_A) = 0 Then
myOverallNumberPallets = myQuantity \ myNumberSheetsPallet_A
myFullPallets = myOverallNumberPallets
' Here we have just calculated the overall number of pallets that the customer's order will use

myNumberSheetsStack_1 = myNumberSheetsStack_A
myNumberSheetsPallet_1 = myNumberSheetsPallet_A
Sheet1.Cells(25, 18) = NO
' There are NO part pallets

Sheet1.Cells(25, 22) = NO
' The redistribution action will not be done because there are no part pallets

CommandButton15.Enabled = False
' The number of sheets per stack2 will be enabled when we redistribute the sheets of the last part pallet onto the other pallets. This button will be enabled when we have a "B", "E", "EB", "EE", or "NE" flutes. And when the overall quantity of sheets in the last part pallet is less than the half of the maximum quantity that can be loaded per pallet

CommandButton16.Enabled = False
' The number of sheets per pallet2 will be enabled when we redistribute the sheets of the last part pallet onto the other pallets. This button will be enabled when we have a "B", "E", "EB", "EE", or "NE" flutes. And when the overall quantity of sheets in the last part pallet is less than the half of the maximum quantity that can be loaded per pallet

CommandButton9.Enabled = False
' There are no part pallets so the button of the number of sheets in the last part pallet will not be enabled

ElseIf (myQuantity Mod myNumberSheetsPallet_A) <> 0 Then
myOverallNumberPallets = (myQuantity \ myNumberSheetsPallet_A) + 1
' Here we have just calculated the overall number of pallets that the customer's order will use

If (myQuantity Mod myNumberSheetsPallet_A) < (myNumberSheetsPallet_A \ 2) Then
Sheet1.Cells(25, 18) = NO
' There are NO part pallets

Sheet1.Cells(25, 22) = YES
' The redistribution action will be done because there are no part pallets

CommandButton15.Enabled = True
' The number of sheets per stack2 will be enabled when we redistribute the sheets of the last part pallet onto the other pallets. This button will be enabled when we have a "B", "E", "EB", "EE", or "NE" flutes. And when the overall quantity of sheets in the last part pallet is less than the half of the maximum quantity that can be loaded per pallet

CommandButton16.Enabled = True
' The number of sheets per pallet2 will be enabled when we redistribute the sheets of the last part pallet onto the other pallets. This button will be enabled when we have a "B", "E", "EB", "EE", or "NE" flutes. And when the overall quantity of sheets in the last part pallet is less than the half of the maximum quantity that can be loaded per pallet

CommandButton9.Enabled = False
' There are no part pallets so the button of the number of sheets in the last part pallet will not be enabled

mySheetsRedistribute = myQuantity Mod myNumberSheetsPallet_A
' If the number of sheets in the last part pallet is less than the half of the maximum quantity that can be loaded per pallet, then we distribute those sheets in the last part pallet onto the other ones

myFullPallets = myOverallNumberPallets - 1

If mySheetsRedistribute / (myNumberStacks * (myOverallNumberPallets - 1)) < 1 Then
' The overall number of stacks is bigger than the number of sheets of the last part pallet that should be distributed

myNumberSheetsStack_1 = myNumberSheetsStack_A + 1
myNumberSheetsPallet_1 = myNumberStacks * (myNumberSheetsStack_A + 1)
myNumberStacks_1 = mySheetsRedistribute
myNumberSheetsStack_2 = myNumberSheetsStack_A
myNumberSheetsPallet_2 = myNumberSheetsPallet_A
myNumberStacks_2 = myNumberStacks * myOverallNumberPallets - myNumberStacks_1

ElseIf mySheetsRedistribute / (myNumberStacks * (myOverallNumberPallets - 1)) >= 1 Then
' The overall number of stacks is smaller than the number of sheets of the last part pallet that should be distributed

myNumberSheetsStack_2 = (mySheetsRedistribute \ (myNumberStacks * (myOverallNumberPallets - 1))) + myNumberSheetsStack_A
myNumberSheetsPallet_2 = myNumberSheetsStack_2 * myNumberStacks
myNumberStacks_1 = mySheetsRedistribute Mod (myNumberStacks * (myOverallNumberPallets - 1))
myNumberStacks_2 = ((myOverallNumberPallets - 1) * myNumberStacks) - myNumberStacks_1
myNumberSheetsStacks_1 = myNumberSheetsStack_2 + 1
myNumberSheetsPallet_1 = myNumberSheetsStack_1 * myNumberStacks
End If

ElseIf (myQuantity Mod myNumberSheetsPallet_A) >= (myNumberSheetsPallet_A \ 2) Then
Sheet1.Cells(25, 18) = YES
' There are part pallets

Sheet1.Cells(25, 22) = NO
' The redistribution action will not be done because there are no part pallets

CommandButton15.Enabled = False
' The number of sheets per stack2 will be enabled when we redistribute the sheets of the last part pallet onto the other pallets. This button will be enabled when we have a "B", "E", "EB", "EE", or "NE" flutes. And when the overall quantity of sheets in the last part pallet is less than the half of the maximum quantity that can be loaded per pallet

CommandButton16.Enabled = False
' The number of sheets per pallet2 will be enabled when we redistribute the sheets of the last part pallet onto the other pallets. This button will be enabled when we have a "B", "E", "EB", "EE", or "NE" flutes. And when the overall quantity of sheets in the last part pallet is less than the half of the maximum quantity that can be loaded per pallet

CommandButton9.Enabled = True
' There are part pallets so the button of the number of sheets in the last part pallet will be enabled

myFullPallets = myOverallNumberPallets - 1
mySheetsRedistribute = myQuantity Mod myNumberSheetsPallet_A
myNumberSheetsStacks_1 = myNumberSheetsStack_A
myNumberSheetsPallet_1 = myNumberSheetsPallet_A
myNumberStacks_1 = (myOverallNumberPallets - 1) * myNumberStacks
myNumberSheetsLeftPartPallet = myQuantity Mod myNumberSheetsPallet_A
' Next, the tolerance of each customer will be aplied

If ComboBox6.Text = "+3%" Then
myMaximumAddingSheetsPartPallet = (myQuantity * 3) \ 100

ElseIf ComboBox6.Text = "5%" Then
myMaximumAddingSheetsPartPallet = (myQuantity * 5) \ 100

ElseIf ComboBox6.Text = "10%" Then
myMaximumAddingSheetsPartPallet = (myQuantity * 10) \ 100
End If

If myNumberSheetsLeftPartPallet <= myMaximumAddingSheetsPartPallet Then
myNumberSheetsPartPallet = mySheetsRedistribute + myNumberSheetsLeftPartPallet
' The sheets that are left to get a full pallet are less than the maximum number of sheets that can be applied with the tolerance

ElseIf myNumberSheetsLeftPartPallet > myMaximumAddingSheetsPartPallet Then
myNumberSheetsPartPallet = mySheetsRedistribute + myMaximumAddingSheetsPartPallet
' The sheets that are left to get a full pallet are more than the maximum number of sheets that can be applied with the tolerance

End If
End If
End If

ElseIf ComboBox7.Text = "F" Or ComboBox7.Text = "N" Then

If myQuantity Mod myNumberSheetsPallet_A = 0 Then
myFullPallets = myOverallNumberPallets
myOverallNumberPallets = myQuantity \ myNumberSheetsPallet_A
' Here we have just calculated the overall number of pallets that the customer's order will use

myNumberSheetsStack_1 = myNumberSheetsStack_A
myNumberSheetsPallet_1 = myNumberSheetsPallet_A
myNumberStacks_1 = myOverallNumberPallets * myNumberStacks
Sheet1.Cells(25, 18) = NO
' There are NO part pallets

Sheet1.Cells(25, 22) = NO
' The redistribution action will not be done because there are no part pallets

CommandButton15.Enabled = False
' The number of sheets per stack2 will be enabled when we redistribute the sheets of the last part pallet onto the other pallets. This button will be enabled when we have a "B", "E", "EB", "EE", or "NE" flutes. And when the overall quantity of sheets in the last part pallet is less than the half of the maximum quantity that can be loaded per pallet

CommandButton16.Enabled = False
' The number of sheets per pallet2 will be enabled when we redistribute the sheets of the last part pallet onto the other pallets. This button will be enabled when we have a "B", "E", "EB", "EE", or "NE" flutes. And when the overall quantity of sheets in the last part pallet is less than the half of the maximum quantity that can be loaded per pallet

CommandButton9.Enabled = False
' There are no part pallets so the button of the number of sheets in the last part pallet will not be enabled

ElseIf (myQuantity Mod myNumberSheetsPallet_A) <> 0 Then
myOverallNumberPallets = (myQuantity \ myNumberSheetsPallet_A) + 1
' Here we have just calculated the overall number of pallets that the customer's order will use

myFullPallets = myOverallNumberPallets - 1
Sheet1.Cells(25, 18) = YES
' There are part pallets

Sheet1.Cells(25, 22) = NO
' The redistribution action will not be done because there are no part pallets

CommandButton15.Enabled = False
' The number of sheets per stack2 will be enabled when we redistribute the sheets of the last part pallet onto the other pallets. This button will be enabled when we have a "B", "E", "EB", "EE", or "NE" flutes. And when the overall quantity of sheets in the last part pallet is less than the half of the maximum quantity that can be loaded per pallet

CommandButton16.Enabled = False
' The number of sheets per pallet2 will be enabled when we redistribute the sheets of the last part pallet onto the other pallets. This button will be enabled when we have a "B", "E", "EB", "EE", or "NE" flutes. And when the overall quantity of sheets in the last part pallet is less than the half of the maximum quantity that can be loaded per pallet

CommandButton9.Enabled = True
' There are part pallets so the button of the number of sheets in the last part pallet will be enabled

mySheetsRedistribute = myQuantity Mod myNumberSheetsPallet_A
myNumberSheetsStacks_1 = myNumberSheetsStack_A
myNumberSheetsPallet_1 = myNumberSheetsPallet_A
myNumberStacks_1 = (myOverallNumberPallets - 1) * myNumberStacks
myNumberSheetsLeftPartPallet = myQuantity Mod myNumberSheetsPallet_A
' Next, the tolerance of each customer will be aplied

If ComboBox6.Text = "+3%" Then
myMaximumAddingSheetsPartPallet = (myQuantity * 3) \ 100

ElseIf ComboBox6.Text = "5%" Then
myMaximumAddingSheetsPartPallet = (myQuantity * 5) \ 100

ElseIf ComboBox6.Text = "10%" Then
myMaximumAddingSheetsPartPallet = (myQuantity * 10) \ 100
End If

If myNumberSheetsLeftPartPallet <= myMaximumAddingSheetsPartPallet Then
myNumberSheetsPartPallet = mySheetsRedistribute + myNumberSheetsLeftPartPallet
' The sheets that are left to get a full pallet are less than the maximum number of sheets that can be applied with the tolerance

ElseIf myNumberSheetsLeftPartPallet > myMaximumAddingSheetsPartPallet Then
myNumberSheetsPartPallet = mySheetsRedistribute + myMaximumAddingSheetsPartPallet
' The sheets that are left to get a full pallet are more than the maximum number of sheets that can be applied with the tolerance

End If
End If
End If

Sheet1.Cells(27, 18) = myNumberSheetsStack_1
Sheet1.Cells(29, 18) = myNumberSheetsPallet_1
Sheet1.Cells(31, 18) = myNumberSheetsStack_2
Sheet1.Cells(33, 18) = myNumberSheetsPallet_2
Sheet1.Cells(29, 22) = myNumberStacks_1
Sheet1.Cells(33, 22) = myNumberStacks_2
Sheet1.Cells(27, 22) = myNumberSheetsPartPallet
Sheet1.Cells(23, 18) = myFullPallets

Pallet_Width2(0) = 800
Pallet_Length2(0) = 800
Pallet_Width2(1) = 900
Pallet_Length2(1) = 800
Pallet_Width2(2) = 1000
Pallet_Length2(2) = 800
Pallet_Width2(3) = 1100
Pallet_Length2(3) = 800
Pallet_Width2(4) = 1200
Pallet_Length2(4) = 800
Pallet_Width2(5) = 1100
Pallet_Length2(5) = 900
Pallet_Width2(6) = 1200
Pallet_Length2(6) = 900
Pallet_Width2(7) = 1500
Pallet_Length2(7) = 900
Pallet_Width2(8) = 1000
Pallet_Length2(8) = 1000
Pallet_Width2(9) = 1100
Pallet_Length2(9) = 1000
Pallet_Width2(10) = 1200
Pallet_Length2(10) = 1000
Pallet_Width2(11) = 1400
Pallet_Length2(11) = 1000
Pallet_Width2(12) = 1100
Pallet_Length2(12) = 1100
Pallet_Width2(13) = 1200
Pallet_Length2(13) = 1100
Pallet_Width2(14) = 1600
Pallet_Length2(14) = 1100
Pallet_Width2(15) = 1200
Pallet_Length2(15) = 1200
Pallet_Width2(16) = 1300
Pallet_Length2(16) = 1100
Pallet_Width2(17) = 1300
Pallet_Length2(17) = 800
Pallet_Width2(18) = 1500
Pallet_Length2(18) = 1200
Pallet_Width2(19) = 1400
Pallet_Length2(19) = 1200
Pallet_Width2(20) = 1300
Pallet_Length2(20) = 1000
Pallet_Width2(21) = 1500
Pallet_Length2(21) = 1000
Pallet_Width2(22) = 1600
Pallet_Length2(22) = 800
Pallet_Width2(23) = 1600
Pallet_Length2(23) = 1600

If ComboBox5.Text = "YES" Then

'The customer decides the type of pallet to use
If ComboBox13.Text = "Standard" Then

' The customer requires a standard pallet, so we will join two or one pallet for the following cardboard sheet dimensions
If 1300 <= mySheetWidth <= 1760 And 1600 <= mySheetLength <= 3700 Then
myNumberPallets = 2
ElseIf 1300 > mySheetWidth And 1600 > mySheetLength Then
myNumberPallets = 1

End If

ElseIf ComboBox13.Test = "Bespoke" Then

' The customer requires a bespoke pallet, so we will join two or one pallet for the following cardboard sheet dimensions
If mySheetWidth = 1760 And mySheetLength = 3700 Then
myNumberPallets = 2
ElseIf mySheetWidth < 1760 And mySheetLength < 3700 Then
myNumberPallets = 1

End If
End If

If ComboBox3.Text = "YES" Then

'The customers allows overhang, so we will check if the pallet that the customer has required is correct one to use
If (myNumberPallets * myPalletWidth1) <= mySheetWidth <= (2 * myOverhang + myNumberPallets * myPalletWidth1) And (myNumberPallets * myPalletLength1) <= mySheetLength <= (2 * myOverhang + myNumberPallets * myPalletLength1) Then
myPalletWidth = myPalletWidth1
myPalletLength = myPalletLength1
ElseIf (2 * myOverhang + myNumberPallets * myPalletWidth1) < mySheetWidth < (myNumberPallets * myPalletWidth1) And (2 * myOverhang + myNumberPallets * myPalletLength1) < mySheetLength < (myNumberPallets * myPalletLength1) Then
MsgBox "Introduce a new pallet size to use. The chosen pallet doesn't fit to the dimension of the material"

End If

ElseIf ComboBox3.Text = "NO" Then

'The customers doesn't allow overhang, so we will check if the pallet that the customer has required is correct one to use
If (myNumberPallets * myPalletWidth1) = mySheetWidth And (myNumberPallets * myPalletLength1) = mySheetLength Then
myPalletWidth = myPalletWidth1
myPalletLength = myPalletLength1
ElseIf (myNumberPallets * myPalletWidth1) <> mySheetWidth And (myNumberPallets * myPalletLength1) <> mySheetLength Then
MsgBox "Introduce a new pallet size to use. The chosen pallet doesn't fit to the dimension of the material"

End If
End If

ElseIf ComboBox5.Text = "NO" Then

'We decide the type of pallet to use. We will take standard pallet always because this type of pallet fits better into the lorry
If 1300 <= mySheetWidth <= 1760 And 1600 <= mySheetLength <= 3700 Then
myNumberPallets = 2
ElseIf 1300 > mySheetWidth And 1600 > mySheetLength Then
myNumberPallets = 1

End If

If ComboBox3.Text = "YES" Then

'The customers allows overhang, so we will check if the pallet that the customer has required is correct one to use
i = 0
Do While (2 * myOverhang + myNumberPallets * Pallet_Width2(i)) < mySheetWidth < (myNumberPallets * Pallet_Width2(i)) And (2 * myOverhang + myNumberPallets * Pallet_Length2(i)) < mySheetLength < (myNumberPallets * Pallet_Length2(i)) And i <= 23
myPalletWidth = Pallet_Width2(i)
myPalletLength = Pallet_Length2(i)
i = i + 1
Loop

If i = 24 Then
MsgBox "No standard pallets fit with the customer order"

End If

'We have checked all standard pallets

ElseIf ComboBox3.Text = "NO" Then

'The customers doesn't allow overhang, so we will check if the pallet that the customer has required is correct one to use
i = 0
Do While (myNumberPallets * Pallet_Width2(i)) <> mySheetWidth And (myNumberPallets * Pallet_Length2(i)) <> mySheetLength And i <= 23
myPalletWidth = Pallet_Width2(i)
myPalletLength = Pallet_Length2(i)
i = i + 1
Loop

If i = 24 Then
MsgBox "No standard pallets fit with the customer order"

End If

'We have checked all standard pallets

End If
End If

myNumberPallets = Sheet1.Cells(20, 22)
myPalletLength = Sheet1.Cells(21, 18)
myPalletWidth = Sheet1.Cells(19, 18)

End Sub


THANKS A LOT

OLATZ

 

[PHV]

check the value of myFluteThickness, myNumberSheetsPallet_A, ...

Tip: hit the Debug bouton when the error raises.

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[HarleyQuinn]

Your best bet is to step through your code and see when it errors.

It's obviously going to be a division error, so it'll be worth making sure that any values used in calculations are assigned a value if they are 0/NULL/Blank (this should be done when setting the variable value.

Hope this helps

HarleyQuinn
---------------------------------
The most overlooked advantage to owning a computer is that if they foul up there's no law against wacking them around a little. - Joe Martin

Get the most out of Tek-Tips, read FAQ222-2244 before posting.

 

[Bong]

It looks to me like you never set the value of myNumberStacks

_________________
Bob Rashkin

 

[fumei]

Are you sure "(the variables have been defined previously)"????? Maybe they have been defined, but sure as shootin' ONE of them (at least) has not been given a value. Therefore one of them is 0. It is impossible to say which one, which is why PHV suggested checking the values of myFluteThickness, myPaperWeight, myNumberStacks...

Those are the first three variables (in order) used to divide by. If one of them does not have a non-zero value explicitly given to it, then it is 0, and dividing by it is a Division by zero.

Normal debugging tools should find it.

BTW: could you use the code tags for posting code? Thanks.

faq219-2884

Gerry
My paintings and sculpture

 

[olatzcelaya]

hi all,

I have been checking the program and the thing is that in some point of the program the variables lose their value and they take 0 value. I do not know why, because sometimes is one variable and other times is another variable. I have filled all the inputs with random values, to see if i get some outputs and the error 11 is still appearing. Could somebody help me???Maybe is a problem of how the variables are defined.

All the code is the following one:


Dim myCustomer As String
Dim myNumberStacks As Integer
Dim OverSheets As Integer
Dim myPalletWidth1 As Double
Dim myPalletLength1 As Double
Dim myTolerance As Integer
Dim myQuantity As Integer
Dim mySheetWidth As Double
Dim mySheetLength As Double
Dim myFluteWeight As Double
Dim myFluteThickness As Double
Dim myTakeUpFactor As Double
Dim myOverhang As Integer
Dim myPaperWeight As Double
Dim myLorryHeight As Integer
Dim PaperWeight1 As Double
Dim PaperWeight2 As Integer
Dim PaperWeight3 As Integer
Dim myPalletHeight As Integer
Dim myNumberSheetsPalletWeight As Integer
Dim myMaterialHeight As Double
Dim myNumberSheetsPalletHeight As Integer
Dim myNumberSheetsPallet_1 As Integer
Dim myNumberSheetsStack_1 As Integer
Dim myNumberSheetsStack_A As Integer
Dim myNumberSheetsPallet_A As Integer
Dim myNumberSheetsStack_B As Integer
Dim myNumberSheetsPallet_B As Integer
Dim myOverallNumberPallets As Integer
Dim myFullPallets As Integer
Dim mySheetsRedistribute As Integer
Dim myNumberStacks_1 As Integer
Dim myNumberSheetsStack_2 As Integer
Dim myNumberSheetsPallet_2 As Integer
Dim myNumberStacks_2 As Integer
Dim myNumberSheetsLeftPartPallet As Integer
Dim myMaximumAddingSheetsPartPallet As Integer
Dim myNumberSheetsPartPallets As Integer
Dim Pallet_Width2(24) As Integer
Dim Pallet_Length2(24) As Integer
Dim myNumberPallets As Integer
Dim myPalletWidth As Integer
Dim myPalletLength As Integer
Dim i As Integer


Private Sub Worksheet_Activate()
ComboBox1.Clear
ComboBox1.AddItem "YES"
ComboBox1.AddItem "NO"
ComboBox2.Clear
ComboBox2.AddItem "YES"
ComboBox2.AddItem "NO"
ComboBox3.Clear
ComboBox3.AddItem "YES"
ComboBox3.AddItem "NO"
ComboBox4.Clear
ComboBox4.AddItem "YES"
ComboBox4.AddItem "NO"
ComboBox5.Clear
ComboBox5.AddItem "YES"
ComboBox5.AddItem "NO"
ComboBox6.Clear
ComboBox6.AddItem "+10%"
ComboBox6.AddItem "+5%"
ComboBox6.AddItem "+3%"
ComboBox7.AddItem "B"
ComboBox7.AddItem "E"
ComboBox7.AddItem "EB"
ComboBox7.AddItem "EE"
ComboBox7.AddItem "F"
ComboBox7.AddItem "N"
ComboBox7.AddItem "NE"
ComboBox8.AddItem "Single Face"
ComboBox8.AddItem "Double Face"
ComboBox8.AddItem "Double Wall"
ComboBox9.AddItem "British Lorry"
ComboBox9.AddItem "Irish Lorry"
ComboBox9.AddItem "Scottish Lorry"
ComboBox9.AddItem "European Lorry"
ComboBox10.AddItem "...."
ComboBox10.AddItem "120FBK"
ComboBox10.AddItem "125B"
ComboBox10.AddItem "125C"
ComboBox10.AddItem "125D"
ComboBox10.AddItem "125K"
ComboBox10.AddItem "125L"
ComboBox10.AddItem "125M"
ComboBox10.AddItem "125T"
ComboBox10.AddItem "125W"
ComboBox10.AddItem "150B"
ComboBox10.AddItem "150D"
ComboBox10.AddItem "150FBK"
ComboBox10.AddItem "150K"
ComboBox10.AddItem "150L"
ComboBox10.AddItem "150M"
ComboBox10.AddItem "150T"
ComboBox10.AddItem "150W"
ComboBox10.AddItem "150Y"
ComboBox10.AddItem "200T"
ComboBox10.AddItem "170FBK"
ComboBox10.AddItem "180W"
ComboBox10.AddItem "180Y"
ComboBox10.AddItem "190Y"
ComboBox10.AddItem "200K"
ComboBox10.AddItem "200L"
ComboBox10.AddItem "200M"
ComboBox10.AddItem "180D"
ComboBox10.AddItem "425BDC"
ComboBox10.AddItem "185Y"
ComboBox10.AddItem "135Y"
ComboBox10.AddItem "300K"
ComboBox10.AddItem "300T"
ComboBox11.AddItem "...."
ComboBox11.AddItem "120FBK"
ComboBox11.AddItem "125B"
ComboBox11.AddItem "125C"
ComboBox11.AddItem "125D"
ComboBox11.AddItem "125K"
ComboBox11.AddItem "125L"
ComboBox11.AddItem "125M"
ComboBox11.AddItem "125T"
ComboBox11.AddItem "125W"
ComboBox11.AddItem "150B"
ComboBox11.AddItem "150D"
ComboBox11.AddItem "150FBK"
ComboBox11.AddItem "150K"
ComboBox11.AddItem "150L"
ComboBox11.AddItem "150M"
ComboBox11.AddItem "150T"
ComboBox11.AddItem "150W"
ComboBox11.AddItem "150Y"
ComboBox11.AddItem "200T"
ComboBox11.AddItem "170FBK"
ComboBox11.AddItem "180W"
ComboBox11.AddItem "180Y"
ComboBox11.AddItem "190Y"
ComboBox11.AddItem "200K"
ComboBox11.AddItem "200L"
ComboBox11.AddItem "200M"
ComboBox11.AddItem "180D"
ComboBox11.AddItem "425BDC"
ComboBox11.AddItem "185Y"
ComboBox11.AddItem "135Y"
ComboBox11.AddItem "300K"
ComboBox11.AddItem "300T"
ComboBox12.AddItem "...."
ComboBox12.AddItem "120FBK"
ComboBox12.AddItem "125B"
ComboBox12.AddItem "125C"
ComboBox12.AddItem "125D"
ComboBox12.AddItem "125K"
ComboBox12.AddItem "125L"
ComboBox12.AddItem "125M"
ComboBox12.AddItem "125T"
ComboBox12.AddItem "125W"
ComboBox12.AddItem "150B"
ComboBox12.AddItem "150D"
ComboBox12.AddItem "150FBK"
ComboBox12.AddItem "150K"
ComboBox12.AddItem "150L"
ComboBox12.AddItem "150M"
ComboBox12.AddItem "150T"
ComboBox12.AddItem "150W"
ComboBox12.AddItem "150Y"
ComboBox12.AddItem "200T"
ComboBox12.AddItem "170FBK"
ComboBox12.AddItem "180W"
ComboBox12.AddItem "180Y"
ComboBox12.AddItem "190Y"
ComboBox12.AddItem "200K"
ComboBox12.AddItem "200L"
ComboBox12.AddItem "200M"
ComboBox12.AddItem "180D"
ComboBox12.AddItem "425BDC"
ComboBox12.AddItem "185Y"
ComboBox12.AddItem "135Y"
ComboBox12.AddItem "300K"
ComboBox12.AddItem "300T"
ComboBox13.AddItem "Standard"
ComboBox13.AddItem "Bespoke"
End Sub

Sub CommandButton1_Click()

On Error GoTo errorhandler
myCustomer = InputBox("Please enter name of customer:", "Pallet Decision")
Sheet1.Cells(3, 5) = myCustomer

errorhandler:
End Sub

Private Sub ComboBox1_Click()

If ComboBox1.Text = "YES" Then
CommandButton12.Enabled = True
' The number of sheets per stacks is specified beforehand

ElseIf ComboBox1.Text = "NO" Then
CommandButton12.Enabled = False
' The number of sheets per stacks is specified later on
End If
End Sub

Sub CommandButton12_Click()

On Error GoTo errorhandler
myNumberSheetsStack = InputBox("Please enter number of sheets per stack:", "Pallet Decision")

Sheet1.Cells(5, 10) = myNumberSheetsStackCustomer

errorhandler:
End Sub

Private Sub ComboBox2_Click()

If ComboBox2.Text = "YES" Then
MsgBox "Number of Stacks is a customer decision"

ElseIf ComboBox2.Text = "NO" Then
MsgBox "Number of Stacks is a planning department decision"
End If
End Sub

Sub CommandButton2_Click()

On Error GoTo errorhandler
myNumberStacks = InputBox("Please enter number of stacks per pallet:", "Pallet Decision")

Sheet1.Cells(7, 10) = myNumberStacks

errorhandler:
End Sub

Private Sub ComboBox5_Click()

If ComboBox5.Text = "YES" Then

' The customer decides the type of pallet to use

CommandButton3.Enabled = True
CommandButton4.Enabled = True
ComboBox13.Enabled = True


ElseIf ComboBox5.Text = "NO" Then

' We decide the type of pallet to use. The cells of pallet width and pallet length are disabled

CommandButton3.Enabled = False
CommandButton4.Enabled = False
ComboBox13.Enabled = False

End If
End Sub

Sub CommandButton3_Click()

On Error GoTo errorhandler
myPalletWidth1 = InputBox("Please enter pallet width:", "Pallet Decision")
Sheet1.Cells(13, 10) = myPalletWidth1

errorhandler:
End Sub

Sub CommandButton4_Click()

On Error GoTo errorhandler
myPalletLength1 = InputBox("Please enter pallet length:", "Pallet Decision")
Sheet1.Cells(15, 10) = myPalletLength1

errorhandler:
End Sub


Sub CommandButton5_Click()

On Error GoTo errorhandler
myQuantity = InputBox("Please enter number of sheets:", "Pallet Decision")
Do While myQuantity < 0 And myQuantity > 30000
myQuantity = InputBox("Please input a valid number of sheet:", "Pallet Decision")

Loop
Sheet1.Cells(19, 5) = myQuantity

errorhandler:
End Sub

Sub CommandButton6_Click()

On Error GoTo errorhandler
mySheetWidth = InputBox("Please enter the width of the sheet:", "Pallet Decision")
Sheet1.Cells(21, 5) = mySheetWidth

errorhandler:
End Sub

Sub CommandButton7_Click()

On Error GoTo errorhandler
mySheetLength = InputBox("Please enter the length of the sheet:", "Pallet Decision")
Sheet1.Cells(23, 5) = mySheetLength

errorhandler:
End Sub

Private Sub ComboBox7_Click()

If ComboBox7.Text = "B" Then
ComboBox1.Enabled = True
ComboBox2.Enabled = True
CommandButton2.Enabled = True
CommandButton12.Enabled = True
myTakeUpFactor = 1.3232
myFluteThickness = 3
myOverhang = 100

ElseIf ComboBox7.Text = "E" Then
ComboBox1.Enabled = True
ComboBox2.Enabled = True
CommandButton2.Enabled = True
CommandButton12.Enabled = True
myTakeUpFactor = 1.2521
myFluteThickness = 1.6
myOverhang = 50

ElseIf ComboBox7.Text = "EB" Then
ComboBox1.Enabled = True
ComboBox2.Enabled = True
CommandButton2.Enabled = True
CommandButton12.Enabled = True
myTakeUpFactor = 2.5753
myFluteThickness = 4.6
myOverhang = 100

ElseIf ComboBox7.Text = "EE" Then
ComboBox1.Enabled = True
ComboBox2.Enabled = True
CommandButton2.Enabled = True
CommandButton12.Enabled = True
myTakeUpFactor = 2.5042
myFluteThickness = 3.2
myOverhang = 100

ElseIf ComboBox7.Text = "F" Then
ComboBox1.Enabled = False
ComboBox2.Enabled = False
CommandButton2.Enabled = False
CommandButton12.Enabled = False
myNumberStacks = 1
myTakeUpFactor = 1.22
myFluteThickness = 1.2
myOverhang = 0

ElseIf ComboBox7.Text = "N" Then
ComboBox1.Enabled = False
ComboBox2.Enabled = False
CommandButton2.Enabled = False
CommandButton12.Enabled = False
myNumberStacks = 1
myTakeUpFactor = 1.51
myFluteThickness = 0.6
myOverhang = 0

ElseIf ComboBox7.Text = "NE" Then
ComboBox1.Enabled = True
ComboBox2.Enabled = True
CommandButton2.Enabled = True
CommandButton12.Enabled = True
myTakeUpFactor = 2.7621
myFluteThickness = 2.2
myOverhang = 0

End If

If ComboBox3.Text = "YES" Then
myFluteWeight = (myTakeUpFactor * 100) * (myOverhang + mySheetWidth) * (myOverhang + mySheetLength)
' The overhang is allowed

ElseIf ComboBox3.Text = "NO" Then
myFluteWeight = myTakeUpFactor * 100 * mySheetWidth * mySheetLength
' The overhang is not allowed

End If
End Sub

Private Sub ComboBox8_Click()

If ComboBox8.Text = "Single Face" Then
ComboBox10.Enabled = True
ComboBox11.Enabled = False
ComboBox12.Enabled = False

If ComboBox3.Text = "YES" Then
myPaperWeight = PaperWeight1 * (mySheetWidth + myOverhang) * (mySheetLength + myOverhang) + myFluteWeight
' The overhang is allowed

ElseIf ComboBox3.Text = "NO" Then
myPaperWeight = PaperWeight1 * mySheetWidth * mySheetLength + myFluteWeight
' The overhang is not allowed

End If

ElseIf ComboBox8.Text = "Double Face" Then
ComboBox10.Enabled = True
ComboBox11.Enabled = True
ComboBox12.Enabled = False

If ComboBox3.Text = "YES" Then
myPaperWeight = (PaperWeight1 + PaperWeight2) * (mySheetWidth + myOverhang) * (mySheetLength + myOverhang) + myFluteWeight
' The overhang is allowed

ElseIf ComboBox3.Text = "NO" Then
myPaperWeight = (PaperWeight1 + PaperWeight2) * mySheetWidth * mySheetLength + myFluteWeight
' The overhang is not allowed

End If

ElseIf ComboBox8.Text = "Double Wall" Then
ComboBox10.Enabled = True
ComboBox11.Enabled = True
ComboBox12.Enabled = True

If ComboBox3.Text = "YES" Then
myPaperWeight = (PaperWeight1 + PaperWeight2 + PaperWeight3) * (mySheetWidth + myOverhang) * (mySheetLength + myOverhang) + myFluteWeight
' The overhang is allowed

ElseIf ComboBox3.Text = "NO" Then
myPaperWeight = (PaperWeight1 + PaperWeight2 + PaperWeight3) * mySheetWidth * mySheetLength + myFluteWeight
' The overhang is not allowed

End If
End If
End Sub

Private Sub ComboBox9_Click()

If ComboBox9.Text = "British Lorry" Then
myLorryHeight = 3000

ElseIf ComboBox9.Text = "Irish Lorry" Then
myLorryHeight = 2600

ElseIf ComboBox9.Text = "Scottish Lorry" Then
myLorryHeight = 3000

ElseIf ComboBox9.Text = "European Lorry" Then
myLorryHeight = 2700
End If
End Sub

Private Sub ComboBox10_Click()

If ComboBox10.Text = "120FBK" Or ComboBox10.Text = "125L" Then
PaperWeight1 = 0.00012

ElseIf ComboBox10.Text = "125B" Or ComboBox10.Text = "125D" Or ComboBox10.Text = "125M" Then
PaperWeight1 = 0.000125

ElseIf ComboBox10.Text = "125C" Or ComboBox10.Text = "125T" Then
PaperWeight1 = 0.00011

ElseIf ComboBox10.Text = "125K" Or ComboBox10.Text = "125W" Then
PaperWeight1 = 0.000115

ElseIf ComboBox10.Text = "150B" Or ComboBox10.Text = "150D" Or ComboBox10.Text = "150FBK" Or ComboBox10.Text = "150Y" Then
PaperWeight1 = 0.00015

ElseIf ComboBox10.Text = "150K" Or ComboBox10.Text = "150M" Or ComboBox10.Text = "150W" Then
PaperWeight1 = 0.00014

ElseIf ComboBox10.Text = "200T" Or ComboBox10.Text = "180W" Or ComboBox10.Text = "200K" Or ComboBox10.Text = "200M" Then
PaperWeight1 = 0.000135

ElseIf ComboBox10.Text = "150L" Or ComboBox10.Text = "150T" Or ComboBox10.Text = "135Y" Then
PaperWeight1 = 0.000175

ElseIf ComboBox10.Text = "170FBK" Then
PaperWeight1 = 0.00017

ElseIf ComboBox10.Text = "200L" Or ComboBox10.Text = "180Y" Or ComboBox10.Text = "180D" Then
PaperWeight1 = 0.00018

ElseIf ComboBox10.Text = "190Y" Or ComboBox10.Text = "185Y" Then
PaperWeight1 = 0.000185

ElseIf ComboBox10.Text = "425BDC" Then
PaperWeight1 = 0.000425

ElseIf ComboBox10.Text = "300K" Then
PaperWeight1 = 0.000275

ElseIf ComboBox10.Text = "300T" Then
PaperWeight1 = 0.00027

ElseIf ComboBox10.Text = "..." Then
PaperWeight1 = 0.00009

End If
End Sub

Private Sub ComboBox11_Click()

If ComboBox11.Text = "120FBK" Or ComboBox11.Text = "125L" Then
PaperWeight2 = 0.00012

ElseIf ComboBox11.Text = "125B" Or ComboBox11.Text = "125D" Or ComboBox11.Text = "125M" Then
PaperWeight2 = 0.000125

ElseIf ComboBox11.Text = "125C" Or ComboBox11.Text = "125T" Then
PaperWeight2 = 0.00011

ElseIf ComboBox11.Text = "125K" Or ComboBox11.Text = "125W" Then
PaperWeight2 = 0.000115

ElseIf ComboBox11.Text = "150B" Or ComboBox11.Text = "150D" Or ComboBox11.Text = "150FBK" Or ComboBox11.Text = "150Y" Then
PaperWeight2 = 0.00015

ElseIf ComboBox11.Text = "150K" Or ComboBox11.Text = "150M" Or ComboBox11.Text = "150W" Then
PaperWeight2 = 0.00014

ElseIf ComboBox11.Text = "200T" Or ComboBox11.Text = "180W" Or ComboBox11.Text = "200K" Or ComboBox11.Text = "200M" Then
PaperWeight2 = 0.000135

ElseIf ComboBox11.Text = "150L" Or ComboBox11.Text = "150T" Or ComboBox11.Text = "135Y" Then
PaperWeight2 = 0.000175

ElseIf ComboBox11.Text = "170FBK" Then
PaperWeight2 = 0.00017

ElseIf ComboBox11.Text = "200L" Or ComboBox11.Text = "180Y" Or ComboBox11.Text = "180D" Then
PaperWeight2 = 0.00018

ElseIf ComboBox11.Text = "190Y" Or ComboBox11.Text = "185Y" Then
PaperWeight2 = 0.000185

ElseIf ComboBox11.Text = "425BDC" Then
PaperWeight2 = 0.000425

ElseIf ComboBox11.Text = "300K" Then
PaperWeight2 = 0.000275

ElseIf ComboBox11.Text = "300T" Then
PaperWeight2 = 0.00027

ElseIf ComboBox11.Text = "..." Then
PaperWeight2 = 0.00009

End If
End Sub

Private Sub ComboBox12_Click()

If ComboBox12.Text = "120FBK" Or ComboBox12.Text = "125L" Then
PaperWeight3 = 0.00012

ElseIf ComboBox12.Text = "125B" Or ComboBox12.Text = "125D" Or ComboBox12.Text = "125M" Then
PaperWeight3 = 0.000125

ElseIf ComboBox12.Text = "125C" Or ComboBox12.Text = "125T" Then
PaperWeight3 = 0.00011

ElseIf ComboBox12.Text = "125K" Or ComboBox12.Text = "125W" Then
PaperWeight3 = 0.000115

ElseIf ComboBox12.Text = "150B" Or ComboBox12.Text = "150D" Or ComboBox12.Text = "150FBK" Or ComboBox12.Text = "150Y" Then
PaperWeight3 = 0.00015

ElseIf ComboBox12.Text = "150K" Or ComboBox12.Text = "150M" Or ComboBox12.Text = "150W" Then
PaperWeight3 = 0.00014

ElseIf ComboBox12.Text = "200T" Or ComboBox12.Text = "180W" Or ComboBox12.Text = "200K" Or ComboBox12.Text = "200M" Then
PaperWeight3 = 0.000135

ElseIf ComboBox12.Text = "150L" Or ComboBox12.Text = "150T" Or ComboBox12.Text = "135Y" Then
PaperWeight3 = 0.000175

ElseIf ComboBox12.Text = "170FBK" Then
PaperWeight3 = 0.00017

ElseIf ComboBox12.Text = "200L" Or ComboBox12.Text = "180Y" Or ComboBox12.Text = "180D" Then
PaperWeight3 = 0.00018

ElseIf ComboBox12.Text = "190Y" Or ComboBox12.Text = "185Y" Then
PaperWeight3 = 0.000185

ElseIf ComboBox12.Text = "425BDC" Then
PaperWeight3 = 0.000425

ElseIf ComboBox12.Text = "300K" Then
PaperWeight3 = 0.000275

ElseIf ComboBox12.Text = "300T" Then
PaperWeight3 = 0.00027

ElseIf ComboBox12.Text = "..." Then
PaperWeight3 = 0.00009

End If
End Sub

Private Sub ComboBox13_Click()

If ComboBox13.Text = "Standard" Then
myPalletHeight = 130

ElseIf ComboBox13.Text = "Bespoke" Then
myPalletHeight = 142

End If
End Sub

Private Sub CommandButton8_Click()

If CommandButton12.Enabled = False Then
'The customer does not require a specific number of sheets per stack

If ComboBox7.Text = "F" Then
CommandButton15.Enabled = False
' The number of sheets per stack2 will be enabled when we redistribute the sheets of the last part pallet onto the other pallets. This button will be enabled when we have a "B", "E", "EB", "EE", or "NE" flutes. And when the overall quantity of sheets in the last part pallet is less than the half of the maximum quantity that can be loaded per pallet

CommandButton16.Enabled = False
' The number of sheets per pallet2 will be enabled when we redistribute the sheets of the last part pallet onto the other pallets. This button will be enabled when we have a "B", "E", "EB", "EE", or "NE" flutes. And when the overall quantity of sheets in the last part pallet is less than the half of the maximum quantity that can be loaded per pallet

Sheet1.Cells(25, 22) = NO
' If there are cardboard sheets of "F" or "N" flute, the sheets in the last part pallet are not redistribute onto the other pallets. Even if the number of sheets in the last part pallet are less than the half of the maximum quantity. The reason of this is because they are very delicated flutes

If mySheetWidth <= 1200 And mySheetLength <= 1200 Then
myNumberSheetsStack_1 = 900
myNumberSheetsPallet_1 = 900
' If we have "N" or "F" flutes the number of stacks per pallet is always 1

ElseIf mySheetWidth > 1200 And mySheetLength > 1200 Then
myNumberSheetsStack_1 = 700
myNumberSheetsPallet_1 = 700
End If

ElseIf ComboBox7.Text = "N" Then
CommandButton15.Enabled = False
' The number of sheets per stack2 will be enabled when we redistribute the sheets of the last part pallet onto the other pallets. This button will be enabled when we have a "B", "E", "EB", "EE", or "NE" flutes. And when the overall quantity of sheets in the last part pallet is less than the half of the maximum quantity that can be loaded per pallet

CommandButton16.Enabled = False
' The number of sheets per pallet2 will be enabled when we redistribute the sheets of the last part pallet onto the other pallets. This button will be enabled when we have a "B", "E", "EB", "EE", or "NE" flutes. And when the overall quantity of sheets in the last part pallet is less than the half of the maximum quantity that can be loaded per pallet

Sheet1.Cells(25, 22) = NO
' If there are cardboard sheets of "F" or "N" flute, the sheets in the last part pallet are not redistribute onto the other pallets. Even if the number of sheets in the last part pallet are less than the half of the maximum quantity. The reason of this is because they are very delicated flutes

If mySheetWidth <= 1200 And mySheetLength <= 1200 Then
myNumberSheetsStack_1 = 1200
myNumberSheetsPallet_1 = 1200

ElseIf mySheetWidth > 1200 And mySheetLength > 1200 Then
myNumberSheetsStack_1 = 1000
myNumberSheetsPallet_1 = 1000
End If
' In these if's we have defined the number of sheets per stacks and per pallet for "N" and "F" flute

ElseIf ComboBox7.Text = "B" Or ComboBox7.Text = "E" Or ComboBox7.Text = "EB" Or ComboBox7.Text = "EE" Or ComboBox7.Text = "NE" Then
myMaterialHeight = (myLorryHeight - 170 - (2 * myPalletHeight)) / 2
myNumberSheetsPalletHeight = (myMaterialHeight \ myFluteThickness) * myNumberStacks
myNumberSheetsPalletWeight = 750000 \ myPaperWeight

If myNumberSheetsPalletWeight <= myNumberSheetsPalletHeight Then
myNumberSheetsStack_A = myNumberSheetsPalletWeight \ myNumberStacks
myNumberSheetsPallet_A = myNumberSheetsStack_A * myNumberStacks

ElseIf myNumberSheetsPalletWeight > myNumberSheetsPalletHeight Then
myNumberSheetsStack_A = myMaterialHeight \ myFluteThickness
myNumberSheetsPallet_A = (myMaterialHeight \ myFluteThickness) * myNumberStacks
End If
End If
' In these last if's we decide the number of sheets per stack

ElseIf CommandButton12.Enabled = True Then
myNumberSheetsStack_B = Sheet1.Cells(5, 10)
myNumberSheetsPallet_B = myNumberSheetsStack_B * myNumberStacks
myMaterialHeight = (myLorryHeight - 170 - 2 * myPalletHeight) / 2
myNumberSheetsPalletHeight = (myMaterialHeight \ myFluteThickness) * myNumberStacks
myNumberSheetsPalletWeight = 750000 \ myPaperWeight

If myNumberSheetsPalletWeight <= myNumberSheetsPalletHeight Then
Do While myNumberSheetsPallet_B > myNumberSheetsPalletWeight
MsgBox "Introduce a new quantity of number of sheets per stack"
myNumberSheetsPallet_1 = myNumberSheetsStack_B * myNumberStacks
myNumberSheetsStack_1 = myNumberSheetsStack_B
Loop

ElseIf myNumberSheetsPalletWeight > myNumberSheetsPalletHeight Then
Do While myNumberSheetsPallet_B > myNumberSheetsPalletHeight
MsgBox "Introduce a new quantity of number of sheets per stack"
myNumberSheetsPallet_1 = myNumberSheetsStack_B * myNumberStacks
myNumberSheetsStack_1 = myNumberSheetsStack_B
Loop
End If
' In these last if's it is checked that the customer does not exceed the maximum number of sheets per stack

End If
'In the following steps we will checked if there are any part pallets

If ComboBox7.Text = "B" Or ComboBox7.Text = "E" Or ComboBox7.Text = "EB" Or ComboBox7.Text = "EE" Or ComboBox7.Text = "NE" Then

If (myQuantity Mod myNumberSheetsPallet_A) = 0 Then
myOverallNumberPallets = myQuantity \ myNumberSheetsPallet_A
myFullPallets = myOverallNumberPallets
' Here we have just calculated the overall number of pallets that the customer's order will use

myNumberSheetsStack_1 = myNumberSheetsStack_A
myNumberSheetsPallet_1 = myNumberSheetsPallet_A
Sheet1.Cells(25, 18) = NO
' There are NO part pallets

Sheet1.Cells(25, 22) = NO
' The redistribution action will not be done because there are no part pallets

CommandButton15.Enabled = False
' The number of sheets per stack2 will be enabled when we redistribute the sheets of the last part pallet onto the other pallets. This button will be enabled when we have a "B", "E", "EB", "EE", or "NE" flutes. And when the overall quantity of sheets in the last part pallet is less than the half of the maximum quantity that can be loaded per pallet

CommandButton16.Enabled = False
' The number of sheets per pallet2 will be enabled when we redistribute the sheets of the last part pallet onto the other pallets. This button will be enabled when we have a "B", "E", "EB", "EE", or "NE" flutes. And when the overall quantity of sheets in the last part pallet is less than the half of the maximum quantity that can be loaded per pallet

CommandButton9.Enabled = False
' There are no part pallets so the button of the number of sheets in the last part pallet will not be enabled

ElseIf (myQuantity Mod myNumberSheetsPallet_A) <> 0 Then
myOverallNumberPallets = (myQuantity \ myNumberSheetsPallet_A) + 1
' Here we have just calculated the overall number of pallets that the customer's order will use

If (myQuantity Mod myNumberSheetsPallet_A) < (myNumberSheetsPallet_A \ 2) Then
Sheet1.Cells(25, 18) = NO
' There are NO part pallets

Sheet1.Cells(25, 22) = YES
' The redistribution action will be done because there are no part pallets

CommandButton15.Enabled = True
' The number of sheets per stack2 will be enabled when we redistribute the sheets of the last part pallet onto the other pallets. This button will be enabled when we have a "B", "E", "EB", "EE", or "NE" flutes. And when the overall quantity of sheets in the last part pallet is less than the half of the maximum quantity that can be loaded per pallet

CommandButton16.Enabled = True
' The number of sheets per pallet2 will be enabled when we redistribute the sheets of the last part pallet onto the other pallets. This button will be enabled when we have a "B", "E", "EB", "EE", or "NE" flutes. And when the overall quantity of sheets in the last part pallet is less than the half of the maximum quantity that can be loaded per pallet

CommandButton9.Enabled = False
' There are no part pallets so the button of the number of sheets in the last part pallet will not be enabled

mySheetsRedistribute = myQuantity Mod myNumberSheetsPallet_A
' If the number of sheets in the last part pallet is less than the half of the maximum quantity that can be loaded per pallet, then we distribute those sheets in the last part pallet onto the other ones

myFullPallets = myOverallNumberPallets - 1

If mySheetsRedistribute / (myNumberStacks * (myOverallNumberPallets - 1)) < 1 Then
' The overall number of stacks is bigger than the number of sheets of the last part pallet that should be distributed

myNumberSheetsStack_1 = myNumberSheetsStack_A + 1
myNumberSheetsPallet_1 = myNumberStacks * (myNumberSheetsStack_A + 1)
myNumberStacks_1 = mySheetsRedistribute
myNumberSheetsStack_2 = myNumberSheetsStack_A
myNumberSheetsPallet_2 = myNumberSheetsPallet_A
myNumberStacks_2 = myNumberStacks * myOverallNumberPallets - myNumberStacks_1

ElseIf mySheetsRedistribute / (myNumberStacks * (myOverallNumberPallets - 1)) >= 1 Then
' The overall number of stacks is smaller than the number of sheets of the last part pallet that should be distributed

myNumberSheetsStack_2 = (mySheetsRedistribute \ (myNumberStacks * (myOverallNumberPallets - 1))) + myNumberSheetsStack_A
myNumberSheetsPallet_2 = myNumberSheetsStack_2 * myNumberStacks
myNumberStacks_1 = mySheetsRedistribute Mod (myNumberStacks * (myOverallNumberPallets - 1))
myNumberStacks_2 = ((myOverallNumberPallets - 1) * myNumberStacks) - myNumberStacks_1
myNumberSheetsStacks_1 = myNumberSheetsStack_2 + 1
myNumberSheetsPallet_1 = myNumberSheetsStack_1 * myNumberStacks
End If

ElseIf (myQuantity Mod myNumberSheetsPallet_A) >= (myNumberSheetsPallet_A \ 2) Then
Sheet1.Cells(25, 18) = YES
' There are part pallets

Sheet1.Cells(25, 22) = NO
' The redistribution action will not be done because there are no part pallets

CommandButton15.Enabled = False
' The number of sheets per stack2 will be enabled when we redistribute the sheets of the last part pallet onto the other pallets. This button will be enabled when we have a "B", "E", "EB", "EE", or "NE" flutes. And when the overall quantity of sheets in the last part pallet is less than the half of the maximum quantity that can be loaded per pallet

CommandButton16.Enabled = False
' The number of sheets per pallet2 will be enabled when we redistribute the sheets of the last part pallet onto the other pallets. This button will be enabled when we have a "B", "E", "EB", "EE", or "NE" flutes. And when the overall quantity of sheets in the last part pallet is less than the half of the maximum quantity that can be loaded per pallet

CommandButton9.Enabled = True
' There are part pallets so the button of the number of sheets in the last part pallet will be enabled

myFullPallets = myOverallNumberPallets - 1
mySheetsRedistribute = myQuantity Mod myNumberSheetsPallet_A
myNumberSheetsStacks_1 = myNumberSheetsStack_A
myNumberSheetsPallet_1 = myNumberSheetsPallet_A
myNumberStacks_1 = (myOverallNumberPallets - 1) * myNumberStacks
myNumberSheetsLeftPartPallet = myQuantity Mod myNumberSheetsPallet_A
' Next, the tolerance of each customer will be aplied

If ComboBox6.Text = "+3%" Then
myMaximumAddingSheetsPartPallet = (myQuantity * 3) \ 100

ElseIf ComboBox6.Text = "5%" Then
myMaximumAddingSheetsPartPallet = (myQuantity * 5) \ 100

ElseIf ComboBox6.Text = "10%" Then
myMaximumAddingSheetsPartPallet = (myQuantity * 10) \ 100
End If

If myNumberSheetsLeftPartPallet <= myMaximumAddingSheetsPartPallet Then
myNumberSheetsPartPallet = mySheetsRedistribute + myNumberSheetsLeftPartPallet
' The sheets that are left to get a full pallet are less than the maximum number of sheets that can be applied with the tolerance

ElseIf myNumberSheetsLeftPartPallet > myMaximumAddingSheetsPartPallet Then
myNumberSheetsPartPallet = mySheetsRedistribute + myMaximumAddingSheetsPartPallet
' The sheets that are left to get a full pallet are more than the maximum number of sheets that can be applied with the tolerance

End If
End If
End If

ElseIf ComboBox7.Text = "F" Or ComboBox7.Text = "N" Then

If myQuantity Mod myNumberSheetsPallet_A = 0 Then
myFullPallets = myOverallNumberPallets
myOverallNumberPallets = myQuantity \ myNumberSheetsPallet_A
' Here we have just calculated the overall number of pallets that the customer's order will use

myNumberSheetsStack_1 = myNumberSheetsStack_A
myNumberSheetsPallet_1 = myNumberSheetsPallet_A
myNumberStacks_1 = myOverallNumberPallets * myNumberStacks
Sheet1.Cells(25, 18) = NO
' There are NO part pallets

Sheet1.Cells(25, 22) = NO
' The redistribution action will not be done because there are no part pallets

CommandButton15.Enabled = False
' The number of sheets per stack2 will be enabled when we redistribute the sheets of the last part pallet onto the other pallets. This button will be enabled when we have a "B", "E", "EB", "EE", or "NE" flutes. And when the overall quantity of sheets in the last part pallet is less than the half of the maximum quantity that can be loaded per pallet

CommandButton16.Enabled = False
' The number of sheets per pallet2 will be enabled when we redistribute the sheets of the last part pallet onto the other pallets. This button will be enabled when we have a "B", "E", "EB", "EE", or "NE" flutes. And when the overall quantity of sheets in the last part pallet is less than the half of the maximum quantity that can be loaded per pallet

CommandButton9.Enabled = False
' There are no part pallets so the button of the number of sheets in the last part pallet will not be enabled

ElseIf (myQuantity Mod myNumberSheetsPallet_A) <> 0 Then
myOverallNumberPallets = (myQuantity \ myNumberSheetsPallet_A) + 1
' Here we have just calculated the overall number of pallets that the customer's order will use

myFullPallets = myOverallNumberPallets - 1
Sheet1.Cells(25, 18) = YES
' There are part pallets

Sheet1.Cells(25, 22) = NO
' The redistribution action will not be done because there are no part pallets

CommandButton15.Enabled = False
' The number of sheets per stack2 will be enabled when we redistribute the sheets of the last part pallet onto the other pallets. This button will be enabled when we have a "B", "E", "EB", "EE", or "NE" flutes. And when the overall quantity of sheets in the last part pallet is less than the half of the maximum quantity that can be loaded per pallet

CommandButton16.Enabled = False
' The number of sheets per pallet2 will be enabled when we redistribute the sheets of the last part pallet onto the other pallets. This button will be enabled when we have a "B", "E", "EB", "EE", or "NE" flutes. And when the overall quantity of sheets in the last part pallet is less than the half of the maximum quantity that can be loaded per pallet

CommandButton9.Enabled = True
' There are part pallets so the button of the number of sheets in the last part pallet will be enabled

mySheetsRedistribute = myQuantity Mod myNumberSheetsPallet_A
myNumberSheetsStacks_1 = myNumberSheetsStack_A
myNumberSheetsPallet_1 = myNumberSheetsPallet_A
myNumberStacks_1 = (myOverallNumberPallets - 1) * myNumberStacks
myNumberSheetsLeftPartPallet = myQuantity Mod myNumberSheetsPallet_A
' Next, the tolerance of each customer will be aplied

If ComboBox6.Text = "+3%" Then
myMaximumAddingSheetsPartPallet = (myQuantity * 3) \ 100

ElseIf ComboBox6.Text = "5%" Then
myMaximumAddingSheetsPartPallet = (myQuantity * 5) \ 100

ElseIf ComboBox6.Text = "10%" Then
myMaximumAddingSheetsPartPallet = (myQuantity * 10) \ 100
End If

If myNumberSheetsLeftPartPallet <= myMaximumAddingSheetsPartPallet Then
myNumberSheetsPartPallet = mySheetsRedistribute + myNumberSheetsLeftPartPallet
' The sheets that are left to get a full pallet are less than the maximum number of sheets that can be applied with the tolerance

ElseIf myNumberSheetsLeftPartPallet > myMaximumAddingSheetsPartPallet Then
myNumberSheetsPartPallet = mySheetsRedistribute + myMaximumAddingSheetsPartPallet
' The sheets that are left to get a full pallet are more than the maximum number of sheets that can be applied with the tolerance

End If
End If
End If

Sheet1.Cells(27, 18) = myNumberSheetsStack_1
Sheet1.Cells(29, 18) = myNumberSheetsPallet_1
Sheet1.Cells(31, 18) = myNumberSheetsStack_2
Sheet1.Cells(33, 18) = myNumberSheetsPallet_2
Sheet1.Cells(29, 22) = myNumberStacks_1
Sheet1.Cells(33, 22) = myNumberStacks_2
Sheet1.Cells(27, 22) = myNumberSheetsPartPallet
Sheet1.Cells(23, 18) = myFullPallets

Pallet_Width2(0) = 800
Pallet_Length2(0) = 800
Pallet_Width2(1) = 900
Pallet_Length2(1) = 800
Pallet_Width2(2) = 1000
Pallet_Length2(2) = 800
Pallet_Width2(3) = 1100
Pallet_Length2(3) = 800
Pallet_Width2(4) = 1200
Pallet_Length2(4) = 800
Pallet_Width2(5) = 1100
Pallet_Length2(5) = 900
Pallet_Width2(6) = 1200
Pallet_Length2(6) = 900
Pallet_Width2(7) = 1500
Pallet_Length2(7) = 900
Pallet_Width2(8) = 1000
Pallet_Length2(8) = 1000
Pallet_Width2(9) = 1100
Pallet_Length2(9) = 1000
Pallet_Width2(10) = 1200
Pallet_Length2(10) = 1000
Pallet_Width2(11) = 1400
Pallet_Length2(11) = 1000
Pallet_Width2(12) = 1100
Pallet_Length2(12) = 1100
Pallet_Width2(13) = 1200
Pallet_Length2(13) = 1100
Pallet_Width2(14) = 1600
Pallet_Length2(14) = 1100
Pallet_Width2(15) = 1200
Pallet_Length2(15) = 1200
Pallet_Width2(16) = 1300
Pallet_Length2(16) = 1100
Pallet_Width2(17) = 1300
Pallet_Length2(17) = 800
Pallet_Width2(18) = 1500
Pallet_Length2(18) = 1200
Pallet_Width2(19) = 1400
Pallet_Length2(19) = 1200
Pallet_Width2(20) = 1300
Pallet_Length2(20) = 1000
Pallet_Width2(21) = 1500
Pallet_Length2(21) = 1000
Pallet_Width2(22) = 1600
Pallet_Length2(22) = 800
Pallet_Width2(23) = 1600
Pallet_Length2(23) = 1600

If ComboBox5.Text = "YES" Then

'The customer decides the type of pallet to use
If ComboBox13.Text = "Standard" Then

' The customer requires a standard pallet, so we will join two or one pallet for the following cardboard sheet dimensions
If 1300 <= mySheetWidth <= 1760 And 1600 <= mySheetLength <= 3700 Then
myNumberPallets = 2
ElseIf 1300 > mySheetWidth And 1600 > mySheetLength Then
myNumberPallets = 1

End If

ElseIf ComboBox13.Test = "Bespoke" Then

' The customer requires a bespoke pallet, so we will join two or one pallet for the following cardboard sheet dimensions
If mySheetWidth = 1760 And mySheetLength = 3700 Then
myNumberPallets = 2
ElseIf mySheetWidth < 1760 And mySheetLength < 3700 Then
myNumberPallets = 1

End If
End If

If ComboBox3.Text = "YES" Then

'The customers allows overhang, so we will check if the pallet that the customer has required is correct one to use
If (myNumberPallets * myPalletWidth1) <= mySheetWidth <= (2 * myOverhang + myNumberPallets * myPalletWidth1) And (myNumberPallets * myPalletLength1) <= mySheetLength <= (2 * myOverhang + myNumberPallets * myPalletLength1) Then
myPalletWidth = myPalletWidth1
myPalletLength = myPalletLength1
ElseIf (2 * myOverhang + myNumberPallets * myPalletWidth1) < mySheetWidth < (myNumberPallets * myPalletWidth1) And (2 * myOverhang + myNumberPallets * myPalletLength1) < mySheetLength < (myNumberPallets * myPalletLength1) Then
MsgBox "Introduce a new pallet size to use. The chosen pallet doesn't fit to the dimension of the material"

End If

ElseIf ComboBox3.Text = "NO" Then

'The customers doesn't allow overhang, so we will check if the pallet that the customer has required is correct one to use
If (myNumberPallets * myPalletWidth1) = mySheetWidth And (myNumberPallets * myPalletLength1) = mySheetLength Then
myPalletWidth = myPalletWidth1
myPalletLength = myPalletLength1
ElseIf (myNumberPallets * myPalletWidth1) <> mySheetWidth And (myNumberPallets * myPalletLength1) <> mySheetLength Then
MsgBox "Introduce a new pallet size to use. The chosen pallet doesn't fit to the dimension of the material"

End If
End If

ElseIf ComboBox5.Text = "NO" Then

'We decide the type of pallet to use. We will take standard pallet always because this type of pallet fits better into the lorry
If 1300 <= mySheetWidth <= 1760 And 1600 <= mySheetLength <= 3700 Then
myNumberPallets = 2
ElseIf 1300 > mySheetWidth And 1600 > mySheetLength Then
myNumberPallets = 1

End If

If ComboBox3.Text = "YES" Then

'The customers allows overhang, so we will check if the pallet that the customer has required is correct one to use
i = 0
Do While (2 * myOverhang + myNumberPallets * Pallet_Width2(i)) < mySheetWidth < (myNumberPallets * Pallet_Width2(i)) And (2 * myOverhang + myNumberPallets * Pallet_Length2(i)) < mySheetLength < (myNumberPallets * Pallet_Length2(i)) And i <= 23
myPalletWidth = Pallet_Width2(i)
myPalletLength = Pallet_Length2(i)
i = i + 1
Loop

If i = 24 Then
MsgBox "No standard pallets fit with the customer order"

End If

'We have checked all standard pallets

ElseIf ComboBox3.Text = "NO" Then

'The customers doesn't allow overhang, so we will check if the pallet that the customer has required is correct one to use
i = 0
Do While (myNumberPallets * Pallet_Width2(i)) <> mySheetWidth And (myNumberPallets * Pallet_Length2(i)) <> mySheetLength And i <= 23
myPalletWidth = Pallet_Width2(i)
myPalletLength = Pallet_Length2(i)
i = i + 1
Loop

If i = 24 Then
MsgBox "No standard pallets fit with the customer order"

End If

'We have checked all standard pallets

End If
End If

myNumberPallets = Sheet1.Cells(20, 22)
myPalletLength = Sheet1.Cells(21, 18)
myPalletWidth = Sheet1.Cells(19, 18)

End Sub

ANY HELP WILL BE APPRECIATED A LOT.THANK YOU VERY MUCH!!!!!!!!!!!!!!!!;-)

OLATZ

 

[SkipVought]

OLATZ,

1. Post your CODE using TGML tags. If you do not know what they are, do a FIND in your browser.

2. You need to do some DEBUGGING. Use the Watch Window. Select the Watch Type option to Break when value changes

Skip,
[sub]
Did you hear what happened when the OO programmer lost his library?...
He's now living in OBJECT poverty![/sub]

 

[fumei]

Good grief!

Yes, please use the TGML tags for posting code.

Ummm, I am not trying to be rude, but you are listing yourself as "Programmer", and it seems you need some repriming of basic programming techniques.

1. Debugging for one.

2. Using Select Case in some cases, instead of If...ElseIf.

3. Loading up your Comboboxes with all those individual AddItem instructions. Especially as the three comboboxes get the same values. Ai carumba! This should be done using an array.

4. Loading up a single procedure with that much code. This chould be broken up into smaller called procedures. It makes it easier to read, understand...and to debug.

5. Using default names like CommandButton8. This is a meaningless name.

However, the bottom line is that you are dividing by zero, and it won't let you.

For example, you give myFluteThickness a value in the CommandButton7_Click event. I have no idea what CommanButton7 is supposed to do, but...in any case, CommandButton8 uses:

myNumberSheetsPalletHeight = (myMaterialHeight \ myFluteThickness)

Well...if CommandButton7 was NOT clicked first - thus giving a value to myFluteThickness - then myFluteThickness has NOT been given a value and therefore = 0.....and clicking CommandButton8 will fail on a divide by zero.

Please, do not post your full code again. It is excessively long, especially not using code tags, and proper indents.

This is a basic problem that can be dubugged with the normal debugging tools and methods, and basic error trapping. Stepping through code, using Watch - these would be a good start.

Working on core logic would help. Again, I have no real idea of what is going on, but if you logically need CommandButton7 to be clicked first, then make your logic DO that.

You could do a simple test in CommandButton8 to see if myFluteThickness is, in fact, = 0. That would mean 7 was not clicked.

If there is some logical sequencing, then perhaps values would be better off passed as parameters in NON-commandbutton procedures. Hard to say. This is actually a logic problem, IMO, rather than a numeric one. The essential fact is that you are trying to divide by zero, and it is YOUR responsibility to determine the logic of your code to make sure that does not occur. Use the debugging tools and methods.

There are likely more instances, but as it stands, if you clicked the informatively named CommandButton8 before you clicked CommandButton7, you will indeed quite properly get a Division by zero error.

faq219-2884

Gerry
My paintings and sculpture

 

[PHV]

BTW, have a look here:

http://www.oaltd.co.uk/Indenter/Default.htm

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886