Dwayne
10-29-2017, 12:01 PM
Good day:
I am attempting to create an equation that will update the remaining space in a bin after an item have been stored. After the bin is full -- I want the equation to move to the next bin size. I am playing with four bin sizes and four categories of items (SKU's) with five items in each catagory. The hypothetical dimensions of the bin sizes are:
Bin ID
X
Y
small
5.75
16
medium
7
16
large
11.25
15.25
extra large
12.25
17.5
bulk
12.25
17.5
And info concerning the items is:
SKU ID
X
Y
Location
A1
2
2
A2
2
2
A3
2
2
A4
2
2
A5
2
2
B1
2.25
2.25
B2
2.25
2.25
B3
2.25
2.25
B4
2.25
2.25
B5
2.25
2.25
C1
3
3
C2
3
3
C3
3
3
C4
3
3
C5
3
3
My goal is to have the location automatically update as well at the space remaining in each bin. I tried setting up the equation to work for just the first bin. However, I am not able to declare the correct variable to get the equation to work (cl = column & i = row in my formula).
Sub Data_Update_info()
Dim i AsInteger
For i = 2 To 31
cl = 2
Do While (Data.Cells(i, cl + 2) = " ")
If Data.Cells(i, cl) < Bins.Cells(j, cl) And Data.Cells(i, cl + 1) < Bins.Cells(j, cl + 1) Then
Data.Cells(i, cl) = Bins.Cells(j, cl)
Bins.Cells(j, cl) = Bins.Cells(j, cl) - -Data.Cells(i, cl)
Bins.Cells(j, cl + 1) = Bins.Cells(j, cl + 1) - -Data.Cells(i, cl + 1)
End If
j = j + 1
Loop
Next i
End Sub
All help for this novice is greatly appreciated. In advance -- think you for your help.
Sincerely,
Dwayne
I am attempting to create an equation that will update the remaining space in a bin after an item have been stored. After the bin is full -- I want the equation to move to the next bin size. I am playing with four bin sizes and four categories of items (SKU's) with five items in each catagory. The hypothetical dimensions of the bin sizes are:
Bin ID
X
Y
small
5.75
16
medium
7
16
large
11.25
15.25
extra large
12.25
17.5
bulk
12.25
17.5
And info concerning the items is:
SKU ID
X
Y
Location
A1
2
2
A2
2
2
A3
2
2
A4
2
2
A5
2
2
B1
2.25
2.25
B2
2.25
2.25
B3
2.25
2.25
B4
2.25
2.25
B5
2.25
2.25
C1
3
3
C2
3
3
C3
3
3
C4
3
3
C5
3
3
My goal is to have the location automatically update as well at the space remaining in each bin. I tried setting up the equation to work for just the first bin. However, I am not able to declare the correct variable to get the equation to work (cl = column & i = row in my formula).
Sub Data_Update_info()
Dim i AsInteger
For i = 2 To 31
cl = 2
Do While (Data.Cells(i, cl + 2) = " ")
If Data.Cells(i, cl) < Bins.Cells(j, cl) And Data.Cells(i, cl + 1) < Bins.Cells(j, cl + 1) Then
Data.Cells(i, cl) = Bins.Cells(j, cl)
Bins.Cells(j, cl) = Bins.Cells(j, cl) - -Data.Cells(i, cl)
Bins.Cells(j, cl + 1) = Bins.Cells(j, cl + 1) - -Data.Cells(i, cl + 1)
End If
j = j + 1
Loop
Next i
End Sub
All help for this novice is greatly appreciated. In advance -- think you for your help.
Sincerely,
Dwayne