Need help with inventory control formula using VBA (First time user of VBA)
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).
Code:
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
