Complex VBA function required!!

[RamanS]

This is a very complicated scenario, and i hope someone can help me. I want to write a vba code based on the following scenario.

A cabinet can have a maximum or 11 shelves.
Max number of net cards i can have on the shelf is 3
Max number of trunks i can place on the net card is 2 trunks in each card (6 trunks on a shelf)

Technically, if i have a total of 6 trunks, then i would need only one shelf. But it in my scenario it doesn't work like this.

The trunks should be distributed in such a way that if one shelf goes down, i would still be able to operate with the trunks located on the second shelf. Therefore the setup should be:

Shelf 1 should have 3 trunks
Shelf 2 should have 3 trunks as well.

example 2: If i have 2 trunks, then
Shelf 1 should have 1 trunk
Shelf 2 should have 1 trunk as well.

At the end of the function, i should be able to get the total number of shelves being used. Do i need to use arrays for this type of situation.

Any hints or suggestions would be highly appreciated. Thanks!

[JKing]

Hi, I'm assuming you want to keep the number of shelves to a minimum while trying to balance the number of trunks per shelf.

In the following example I've used three textboxes and a button to run the code.
txtTrunks = The number of trunks entered by the user
txtShelves = Displays the number of shelves needed
txtPer = Displays the number of trunks per shelf
cmdRun = Command button to run

[code=vb]
Private Sub cmdRun_Click()

'Get shelves
If Me.txtTrunks = 1 Then
Me.txtShelves = 1
ElseIf Me.txtTrunks > 1 And Me.txtTrunks < 7 Then
Me.txtShelves = 2
ElseIf Me.txtTrunks > 6 And Me.txtTrunks Mod 6 = 0 Then
Me.txtShelves = Me.txtTrunks / 6
Else
Me.txtShelves = Int(Me.txtTrunk s / 6) + 1
End If

'Get trunks per shelf
If Me.txtTrunks Mod Me.txtShelves = 0 Then
Me.txtPer = Me.txtTrunks / Me.txtShelves
Else
Me.txtPer = Int(Me.txtTrunk s / Me.txtShelves) + 1
End If
End Sub
[/code]