Consulting

Results 1 to 3 of 3

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

  1. #1
    VBAX Newbie
    Joined
    Oct 2017
    Posts
    1
    Location

    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).

    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
    Last edited by SamT; 10-30-2017 at 11:47 AM. Reason: Removed White Space from Code. Added Code Formatting Tags with # Icon

  2. #2
    Moderator VBAX Sage SamT's Avatar
    Joined
    Oct 2006
    Location
    Near Columbia
    Posts
    7,814
    Location
    I corrected the EndIf and the EndSub
    I expect the student to do their homework and find all the errrors I leeve in.


    Please take the time to read the Forum FAQ

  3. #3
    Moderator VBAX Sage SamT's Avatar
    Joined
    Oct 2006
    Location
    Near Columbia
    Posts
    7,814
    Location
    And, you need to initialize j to 1, there is no Column(0).
    Or replace j as a Row number with i-1
    I expect the student to do their homework and find all the errrors I leeve in.


    Please take the time to read the Forum FAQ

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •