Could you explain what you are trying to do?  Not sure what the nesting formula is for, or what you want done with option 3.

But you could just add a textbox and put =DesignWidth/MateralWidth/Qty to get the number, then another textbox for the other if you're trying to compare them.

If you're looking for VBA to automatically give you the best number, you'd need to do something like this with a button on click event and a blank textbox

Dim X As Integer
Dim Y As Integer
X = DesignWidth/MaterialWidth/Qty
Y = DesignHeigh/MaterialHeight/Qty
If X > Y Then
TextBox = Y
ElseIf Y > X Then
TextBox = X
End If

You should probably also wrap the calculations with NZ to prevent errors. You'd also have to figure out how to handle option #3 to put it into the comparison.

I do printing of vinyl. Let me give you an example. Desigh size 80mm x50mm . Media Size 1350mm That means i can fit from left to right 1350/80 = 16 or 1350/50 = 27. If my qty is 200 . I will have 200/16 = 13 rows ( up/Down ) and 16 rows (Left Right )  on the 80mm side. I i rotate  i will have 27 rows ( Left to Right ) and 8 rows ( Up and down ) The bigger the design are the more complex the formula gets. If you get a size bigger than material width like 4000 x 3000 then the formula will have to work out the best way to print . It must always end up with the least amount of material needed to do the work.

Hope this helps

OK So my #1 and #2 are really simple. For #3 though you need to say this is going to take another piece of material correct?

In your example 4000x3000 you would then need two rolls of the 3000 size to complete the job correct? If so, should it always be the same size as the material size put in? Or would you for instance in this case, be able to put a 1000 size roll in to fill the gap?

You could look into these two and see if there's anything there that helps you
Volume and Access Work Order Seminar

Hi Adam, Yes it will take another piece.

No the rolls come in fix sizes of 1370 of 1600 so the rest would be waist.

Regards

Marius

Is that 1370 to 1600 or are they all one size always for the material?

Just wondering if we need to say  with a 2970 size area if its goijg to say we need two of the 1600 pieces or a 1600piece and a 1370?

Hi Adam
The material comes in different sizes that is why we work with media width. When you those the media you will need it pulls the media width and the calc is based on that. You can not use different media width on the same calc. You will either need 3 x 4000 sheets with a waist of 1110 or you will have 3 x 3000 sheets with a waist of 110. In this case the best fit would be the 3 x3000 sheets. Least wait and least media to do the work with.

The calc should look at all options and return with the least media to use.

Hope you understand what i mean.

Thanks

Marius

OK pardon me for jumping in the middle here.  I wonder if this is kind of what you are trying to accomplish?
Not vinyl but same concept.  (My hobby is wood working)

Do you have the option of rotating the image you are "printing" as well?  Horizontal vs Vertical?

Hi Adam. That is exactly what i am trying to do. I also use cut optimizer to work on material that i use for my cnc machine. I would just like a calc to work out the media needed without going to a design or another program to work it out.

Thanks

Marius

Yes you have an option of rotating the image.

Sometimes when the only tool you have in your tool box is a hammer - All problems start to look like a nail.

Don't get me wrong I love Access and not that it can't be done in Access but...
You have to ask what the trade off is?  See the following graphic.  You can import and export the data to / from CutList.
They have done all the heavy lifting so to speak with figures,  graphics,  etc.
It would be easy to save date out or import it back in to Access.

Only you can answer - What would you gain by having it totally in Access?  What kind of development time will you have to invest?  What are the advantages and disadvantages of one over the other?   etc.

Just thoughts to consider.  For me it would be why re-invent the wheel?

Morning Scott. I need this so that people can do a quote without going to all the trouble to do a design and lay-out. We do a lot of quotes based on this calc and it is time consuming.

Thanks

Marius

Marius
See now, that is a perfectly valid reason.  I was thinking that you were trying to lay out the design for production.

Something to think about:  When making the Quote:  Assume you already have the job. :)
(Which it sounds like you have)

By that I mean, as much as possible, when designing the quote form to do the calculations get as much detail as you can to facilitate going from Quote to Order as smooth as possible. You don't want to have to re-enter data to go from Quote to Order to Production.

Now back to your issue.  How do you make the quote now?  Do you have a form to fill out?  How are you figuring out what is best manually?
That would help to have to automate the process to the computer.  Upload scans of paper forms or other info that would help.

Your manual process will dictate how your automated process is performed to a large degree.
Is Length, Width, Quantity the only criteria you use?

Yes

At this stage i have if a couple of statements in excell or we have do do a couple of design lay-outs to see what lay-out would be the best fit with the least amount of vinyl. The statements does not work in access because of the round down and up function that changed. This whole process is time consuming and i would like to change that. I have use a program called QuickEasy before and the programmer and i work for weeks to setup the calc's and it work perfectly but the monthly rental became to much and i decided to write my own program.

Now that I'm re-looking at this, isn't #1 and #2 always going to be 1 Qty? Since the entire point of #3 is to find out how many pieces extra you need?

So if you wanted to do this in access with always the same size pieces, we should be able to do it with a for next loop counter. Before I even try to help though, need some more information.

Is material height irrelevant? You'll just keep rolling it out until the cut point? Or would there be a dimension to that as well?

If so, it gets WAY more complicated if you want to use the waste. Such as if you had a 100x100 room with size 30x30 material, that would come up as 16 qty but if you used your waste that could be done with 12 qty. Hopefully it's not that complex and we're only concerned about the width.

You mentioned you spent time to come up with the calc's.
Do you have that code?  If so copy and paste here.
Also, Post the excel statements and we can look into helping convert for Access

Hi Adam . The Height of the Media are irrelevant as we are working with 50m roles. #1 and #2 will always be one if the Design Width or Design Height are bigger that Media width depending on how it gets rotated. When Design are smaller say 100 x 100 then it could be 13 with a little waist.

Does this make sense?

Regards

Marius

Private Sub MaterialWidth_AfterUpdate()
    'Find the qty of material needed
    Answer1 = DesignWidth / MaterialWidth
    Answer2 = DesignHeight / MaterialWidth
    If Answer1 > 1 And Answer2 > 1 Then
        If Answer1 >= Answer2 Then
        Answer3 = Answer2
        ElseIf Answer2 >= Answer1 Then
        Answer3 = Answer1
        End If
    End If
    
    If Answer3 = 0 Or Answer3 = "" Or IsNull(Answer3) Then
    'Fits on one piece
        If Answer1 >= Answer2 Then
        FinalAnswer = Answer2
        ElseIf Answer2 >= Answer1 Then
        FinalAnswer = Answer1
        End If
    Else
    'Doesn't fit on one piece
    FinalAnswer = Answer3
    End If
    
    'Find the number of pieces needed
    If FinalAnswer - Int(FinalAnswer) = 0 Then
        PiecesNeeded = FinalAnswer
        Else
        PiecesNeeded = Int(FinalAnswer) + 1
    End If
End Sub

Is this what you're looking for then? Seemed to work fine in my little test form, gives you the best answer in final answer and the total number of rolls you'd have to access to do the job.

Thanks you Adam. I will have a look.

Regards