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