VBA LOOP Macro

[IR0505]

Hello To all VBA experts ,

I need a macro for folowing problem .

In below tables there are Panels of diffrent types and lenghts :

there is a max nr of panels that can be packed in on package (wich is different for each type).

on e package may not contain two differnt types of panels .

Altough one package can contain panels of the same type but with different lenghts .

below an ilustration of the probem and on the right marked with green the wandet outcome .
New Bitmap Image.jpg

many thancks in advance for your help.

[EirikDaude]

I'm not sure I undestand what it is you want help with. Maybe you could try to explain in a bit more detail what it is you want the macro to do, and maybe post a sample workbook?

[SamT]

Packages 6 - 9

How do we know they should have number of Items 6 + 11 + 11 + 2

Why not 11 + 11 + 8 ?

LookUp Table

Package Type	Max Qty
A	11
B	15

Algorithm: (Not code)

Var PackageType
Var QtyOrdered
Var NumMaxSizePackages
Var LastPackageQty

NumMaxSizePackages = QtyOrdered / VLookup(PackageType, LookUpTable, 2, False)
LastPackageQty = QtyOrdered Mod VLookup(PackageType, LookUpTable, 2, False)

[IR0505]

Hi,First of all thank you for your replies and apologies for not being clear enough.

Below answers :
EirikDaude: I uploaded a a file with a most precisest description of what i want.

SamT :at Package 6 you first have to fill it till 11 Items (so you have 5 items of Type A with a length of 6000 mm+6 of type A with a length of 4000 mm ) and at package 9 is the left over of 2 items .(you cannot pack 2 Types of Panels in the same Package )

and what i want to do is to create a Macro that helps me to find out how many trucks I need for one order .

In the attachment i gave a better description of the hole Processes .

[westconn1]

In the attachment i gave a better description of the hole Processes .

i can not open the attachment

For Each cel In Range("B3:b200")  ' change to suit
    If IsEmpty(cel) Then
        msg = msg & "Type " & prevcel & " packs = " & pks & vbNewLine
        tpks = tpks + pks
        Exit For
    End If
    If Not prevcel = cel Then
        If r > 0 Then pks = pks + 1
        tot = cel.Offset(, 2)
        If pks > 0 Then msg = msg & "Type " & prevcel & " packs = " & pks & vbNewLine
        tpks = tpks + pks
        pks = 0
        prevcel = cel
        Else
        tot = cel.Offset(, 2) + r
    End If
    pks = pks + tot \ cel.Offset(, 3)
    r = tot Mod cel.Offset(, 3)
Next
MsgBox msg & vbNewLine & "Total packs = " & tpks

see if this can help
declare all variables etc

[SamT]

This will be non-trivial coding, and should be developed in a very systematic method. Most simple VBA coding is done in a linear way, (consider the whole problem >> develop a solution.)

Lucky for you, I have been on many loading docks in many shipping facilities.

Systematic Method:

Clearly Define the goals;

1. Maximize the number of various sized units that can be packed in a given shipping truck's known volume.
2. Provide Package assembly and Truck loading instructions for human use.
   
   • *Multilingual
   
   
   
3. *Report size of unused Truck floor space

* indicates desirable, but not required feature

Clearly define the Business Rules;
Packages

1. The base of all Packages is a 10cm tall Pallet
2. The maximum height of a Package is 120cm
   
   • * Can vary depending on Truck Height?
     
     • See 8 below
   
   
   
3. A Package can only contain one Type of unit
4. A Package can contain Units of different lengths
5. Short Lengths will be place on top of Long lengths
   
   • *Multiple stacks of short Units can be placed on a stack of long Units if the combined length of the short stacks are <= to the length of the underlying long Units
   
   
   
6. In the Truck, short Packages can be stacked on longer Packages
   
   • * 5.1 applies
   
   
   
7. * Uneven height long Packages can be stacked on multiple same height short Packages if the combined length of the short Packages is => the long Package
   
   • *Short Packages can be spaced to be = Long packages

Trucks;

• #The Truck Type is known before the code runs
• #The truck Type is only known after the code runs

A stack is one ore more units in a Package

# indicates mutually exclusive
* indicates unknown to me
5.1 & 7 & 7.1 should be included even though it may not be feasible in the first production code.

Provide all the Parameters, (I already see these in the example you uploaded

1. Unit Types and sizes
2. Pallet Types and sizes *currently, only height. Maybe should include more for Goal # 2.
3. Truck Types and sizes
4. Package size limits

The above is how I would have started your project. Then and only then would I have pondered about the required code segments. After I had a good idea about them, I would have started coding each of them as a separate piece, but with the ability to have each piece call another as needed.

Now this is the Final stage of my Project.

In that case, you only need to apply the first algorithm I offered after modifying it slightly to

Var PackageType 
Var QtyOrdered 
Var NumMaxSizePackages 
Var LastPackageQty 
 
NumMaxSizePackages = QtyOrdered / VLookup(PackageType, LookUpTable, 2, False) 
LastPackageQty = QtyOrdered Mod VLookup(PackageType, LookUpTable, 2, False) 
 
If LastPackageQty.Height < MaxHeight Then 
    Add ShortLengths Until Packagetype.MaxHeight 
Next PackageType.Length.QtyOrdered = (QtyOrdered - ShortLengths.Qty)

In any case, you will need to show us an example book with all your existing code, so we can kludge something up to finish your project.

If, that is, you don't decide to start over, which is a very common choice among professional developers.

[IR0505]

]Hello ,

Thank you all for you replies.

I have to admit i am a noob in means of programming .


I tried to declare the variables although i didn't manage

could you please help me with that ?

Thank you

Sub Macro1()
Dim msg As Integer
Dim pks As Integer
Dim tpks As Integer
Dim r As Integer
Dim tot As Range
Dim cell As Range
Dim prevcel As Range
For Each cel In Range("B3:B200") ' change to suit
    If IsEmpty(cel) Then
        msg = msg & "Type " & prevcel & " packs = " & pks & vbNewLine
        tpks = tpks + pks
        Exit For
    End If
If Not prevcel = cel Then
        If r > 0 Then pks = pks + 1
        tot = cel.Offset(, 2)
        If pks > 0 Then msg = msg & "Type " & prevcel & " packs = " & pks & vbNewLine
        tpks = tpks + pks
        pks = 0
        prevcel = cel
    Else
        tot = cel.Offset(, 2) + r
    End If
    pks = pks + tot \ cel.Offset(, 3)
    r = tot Mod cel.Offset(, 3)
Next
MsgBox msg & vbNewLine & "Total packs = " & tpks
End Sub

[SamT]

In any case, you will need to show us an example book with all your existing code, so we can kludge something up to finish your project.

[IR0505]

Sam ,
I hope you had a very nice New years Party and happy Holidays . I uploaded on below Link my workbook so far .And to be honest i am very proud of myself how i solved the problems of Truck planing : )P . It doesn't uses macros ,because i am not very goods at VBA .but it gives you a very good visualization of how much space is used .Hopefully it will help also others that have to deal with this kind of problem .although i can not come around this Packaging problem whiteout VBA .in order to solve it , could you write me the hole Macro that i have to assign to the command button "PAck" from the sheet Packing so that it writes the result in the same sheet starting with G3, .Thank you in advance for your help .

[IR0505]

i dosen,t let me post the Site where i have uploaded my file how should i proceed?

[IR0505]

I wrote the link in the word Document

[IR0505]

Hi Westcon1,

I am so stupid!

I putted your algorithm in a Command button and it worked .

Although i need the result not in MSG boxes but as in the above illustration in the part that is marked with green.

do you think that is possible ?

Thank you in advance for your answer.

[SamT]

Link in docx above =

http://www.fileshare.ro/e30116437

Size is 17Mb. No typo, seventeen mega bytes.

I'M not downloading 17MBs.

You have to shrink it down to a reasonable size, like smaller than 2MB.

[IR0505]

HI ,

even if i zip de The File it is still 6 MB .

So i uploaded a simplifyd version .

In sheet Packing is the Part with which i Need help .

It should generate the Packages as advised above .

In sheet Prnt Screen i inserted a Preent screen of the rest .