Need help with inventory control formula using VBA (First time user of VBA)

[Dwayne]

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