PDA

View Full Version : VBA LOOP Macro



IR0505
12-22-2013, 07:44 AM
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 .
10999

many thancks in advance for your help.

EirikDaude
12-22-2013, 08:04 AM
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
12-22-2013, 08:27 AM
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
12-23-2013, 03:31 PM
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
12-24-2013, 02:56 AM
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
12-24-2013, 11:03 AM
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;



Maximize the number of various sized units that can be packed in a given shipping truck's known volume.
Provide Package assembly and Truck loading instructions for human use.

*Multilingual



*Report size of unused Truck floor space


* indicates desirable, but not required feature

Clearly define the Business Rules;
Packages


The base of all Packages is a 10cm tall Pallet
The maximum height of a Package is 120cm

* Can vary depending on Truck Height?

See 8 below




A Package can only contain one Type of unit
A Package can contain Units of different lengths
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



In the Truck, short Packages can be stacked on longer Packages

* 5.1 applies



* 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 ;)


Unit Types and sizes
Pallet Types and sizes *currently, only height. Maybe should include more for Goal # 2.
Truck Types and sizes
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
12-25-2013, 12:14 PM
]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
12-25-2013, 01:00 PM
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
01-02-2014, 01:53 PM
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
01-02-2014, 01:59 PM
i dosen,t let me post the Site where i have uploaded my file how should i proceed?

IR0505
01-02-2014, 02:06 PM
I wrote the link in the word Document

IR0505
01-03-2014, 12:10 PM
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
01-03-2014, 05:49 PM
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
01-04-2014, 03:44 AM
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 .