PDA

View Full Version : Need help with inventory control formula using VBA (First time user of VBA)



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

SamT
10-30-2017, 11:48 AM
I corrected the EndIf and the EndSub

SamT
10-31-2017, 08:36 PM
And, you need to initialize j to 1, there is no Column(0).
Or replace j as a Row number with i-1