Excel VBA Course
Excel VBA Course - From Beginner to Expert

200+ Video Lessons
50+ Hours of Video
200+ Excel Guides

Become a master of VBA and Macros in Excel and learn how to automate all of your tasks in Excel with this online course. (No VBA experience required.)

View Course (35% Discount)

Optimizing A Cutting List For Least Waste

Imagine a whole box of lengths of aluminium sections for making windows.

To make those windows I need to cut these lengths into smaller sizes.

I need to do this economically.

How can I use Excel to help me make this a reality.

Firstly you would take a big size out of it and then another size out of the offcut piece.

And then another size out of the offcut left over.

I need to know how many full stock lengths (6500) long it will take to cut all the pieces listed above.

Is it possible?

You need to take into account the quantity of each size and fit it all in together for the most optimal outcome.

See attached example of possible layout.


Excel VBA Course
Excel VBA Course - From Beginner to Expert

200+ Video Lessons 50+ Hours of Instruction 200+ Excel Guides

Become a master of VBA and Macros in Excel and learn how to automate all of your tasks in Excel with this online course. (No VBA experience required.)

View Course (35% Discount)




Similar Topics







I have a given length of steel bar...Example(550 feet)
I have various cut lengths that I can pick from...
14'-10".....13'- 9".. 12'-8"........(up to 6 different lengths)

I need to know the best combination of lengths to cut the long bar into to have minimal scrap left when we get to the end of the 550 feet.....

Cut as many of the longest cut lengths possiable.....Cut as few of the shortest cut lengths possiable

Due to the fact that I will have to convert this to a PLC application when completed, I want to find a mathmatical way to do this inside excel and not use the "solver"....Assuming that I/we can come up with an equation to do this .....
Any and all advice/help would be appricated.....


Sorry for the question. Normally I find answers to my excel questions by going through the help tab or by searching on Google. However, I don't even know what question to ask on this one!?!

Basically I have created a spreadsheet with several columns, but I have one column that lists the shirt size (YS, YM, YL, AS, AM, AL, XL, 2X, 3X) of each person. Is there a formula that I can create that will tabulate the number of sizes (i.e. AS=2, AM=7, etc.)?

In previous years I made a column for each size, and simply placed a "1" in the correct column, and had excel just add the 1's from each column. However, that takes more time and space. I was hoping to streamline it this time around.

Thanks for taking the time to read this post. Any help would be appreciated! Thanks, doug


When using the copy and paste function it is extremely slow to paste the
result. Also the file size seems to be increasing to a much larger size for
no reason.
Any feedback would be appreciated.



I have a file that became too big due to phantom bloat, unused range saved by Excel and all that kind of reasons. Thank to previous posts on that board tackling that issue, I was able to find how to proceed to reduce the file back to its normal size.
But I did so in a beta file (test file). The real file has become so big (103MB!) that Excel cannot even open it anymore! The file contains archive info that we do not have anywhere else.
Is there anyway then to open the file or to reduce its size without opening it (through magics...)? I just honestly don't know how to retrieve that info before deleting that file.

Thank you very much for your help.


I'm working on an Excel file that it's size is about 90 M.B and that makes it very slow to deal with ... is there anyhow to make it faster ???
i use in it alot of formulas and conditional formats
thank u


Hi Everybody,

First post on the forum, have only recently joined and im hoping that someone can help me before my head explodes!

I am trying to prepare a simple Material Quantity List for some Steel materials.
The materials are all in specific sizes.

What i want to do is, as i input the length, width and height of the structure in the spreadsheet, the Material Quantity List will generate the quantity of each piece of material i will need.

Does anybody know of a formula for this. I have been trying to use the IF function and getting absolutely nowhere, all suggestions will be highly appreciated!!


I did a search in here but didn't find an answer so here goes nothing...

I have a spreadsheet with data from A1 to H1 down to A275 to H275. I select all of it, Copy, go into the Sheet2 tab and select A1, Paste Special -> Transpose and it give me the following error message:
Quote:

The information cannot be pasted because the Copy area and the paste area are not the same size and shape. Try one of the following:
- Click a single cell, and then paste.
- Select a rectangle that's the same size and shape, and then paste.

For the life of me, I can't figure out why it's not working! I have done this many times with other spreadsheets. Has anyone ever experienced this? If so, were you able to resolve it and how?

Many Thanks,
Mike


Even though I have used Page Layout to set my margins at .3 left and right, I cannot make my columns as wide as they need to be because the spreadsheet continues to print with one inch margins. I am on a deadline -- Help!


Hi all.

I have set up a workbook that is sent out to lots of different users. They each keep and use their own copy.

I have set it up so that everything looks OK and is visible on MY screen, but I'm conscious that some users may have different screen sizes, different toolbars set up, and so on, which might make some parts not immediately visible to them.

I have set up an auto-execute macro which automatically sets the zoom factor to best fit, for several of the worksheets, and this works fine.
Here's the code that does it.
Code:

Sheets("WELCOME").Select
    Range("A1:N18").Select
    ActiveWindow.Zoom = True


By repeating this code for each worksheet, I can make each one be zoomed just right.

However, the file contains 8 sheets that are all identically laid out, except the number of rows is different.
What I want to do is go to the worksheet that has the largest number of rows (it's always the same worksheet, so I know which one it is), set the zoom factor for THAT worksheet (which I can do, and it always has the same number of rows), and then take THAT zoom factor, whatever it is - and it will vary depending on the user - and apply that to the other worksheets that have a similar layout.

I could just go through each worksheet and zoom it automatically, but that would mean that some of the sheets looked very large, others very small, and I'd like them to have a consistent appearance.
I could also specify a range on each worksheet that was similar to the appropriate range on the longest worksheet, and zoom that automatically, but that's not ideal either, because some of the row heights vary from sheet to sheet, and again I'll end up with different font sizes.

Anyone know how to do this ?


How can I stretch a spreadsheet to fit the page? If I try to make it one page by one page, it changes nothing. If I change the percentage, it becomes two wide...I am just trying to change the height so that it can be read on an 8.5 x 11 piece of paper.


Hello, I am pretty new to excel programming/messing around so I'll refer to those who are much wiser than I am. I am trying to set up a form for work, in this form I want to have a page with a bunch of cells pre-sized. What I want to do is when I insert a picture from a job site, that when this picture gets inserted, it will automatically resize to fit within the cell. I don't have time to manually resize dozens of pictures and if I could get this to work it would be awesome! I tried to search for an answer using the search function, but I kept getting a fatal error about allowable memory? Thanks in advance for you help!


Hi there. I know it sound like a really simple thing but its really stumped me.

Instead of the y axis being on the left hand side, i wish for it to cut at 0,0. I have positive and negative values and I need it to be in the centre instead of on the left.

I have tried formatting both the axis and it would appear that the x axis already cuts the y axis in the right posistion so i need to format the x axis to make the y axis cut it at 0,0. I have already tried typing in 0 instead of 1 and it keeps saying it need to be number more than or equal to 1.

Any help would be greatly appreciated.

Nicole


I have searched and read all the help files. I find the properties of
an object, I see how I can "lock", "size and move with cells" or "not
move with cells". No matter what I select, the object moves off the
screen, when the user, scrolls to the right of the spreadsheet.

Is there a way to lock the position, let's say , in the upper right
corner and have it stay there?

This would be quite useful for an EXIT button, that I have created,
that will close the program without saving (it's a read-only file.)

Thanks to all the wonderful people here that have been so helpful and
give us their valuable insight and time.

Jo




Thought I'd append my experience of the above problem - you can find all sorts of references to it everywhere.

My problem was that a userform defined with Excel at work (containing DT pickers) gave the message in the title when opening it at home. I had a light-bulb moment and wondered whether there was a difference in the version numbers for MSCOMCT2.OCX at work and at home. Turned out the work version was newer. I then copied the MSCOMCT2.* files from work, made a backup of them at home and copied those from work to my C-drive (Windows XP - c:\windows\system32\ ).

No luck. I then rebooted the machine - still no luck.
Then, finally I unregistered the old DLL via
Code:

regsvr32 /u c:\windows\system32\MSCOMCT2.OCX


(not sure if this was necessary, but I didn't think it could hurt). Reregistered the DLL via
Code:

regsvr32 c:\windows\system32\MSCOMCT2.OCX


and what do you know - it worked.

Summa summarum - it could be an idea to check whether the two machines have different version numbers for the MSCOMCT2.OCX files.


Hi, I'm trying to get some macros to combine data from two open workbooks, and I can't figure out the commands to switch from the active workbook to the other open workbook and back. I'm new to VBA and just learning the ropes. All I can figure out is how to switch to another workbook with an exact name. If I record the macro to go to a recent file, for example Window > 1 (filename), the code that I get in VBA is Windows("filename").Activate. This doesn't translate to when I have two different workbooks with different filenames open.

Make sense? I'm a bit confused myself. Maybe just a list of basic workbook-switching techniques or commands would be useful.

- Michael


I would like to know how to make excel calculate which inventory is left in the FIFO method, quantities that are bought first are the first to be sold. Basically where the quantity and price is derived from.

For example:
APPLE
bought 100 on FEB 1 @$1
bought 500 on FEB 1 @$1
bought 1000 on FEB 10 @$1
sold 300 on FEB 12 @$1.5
bought 100 on FEB 13 @$1.1
sold 100 on FEB 13 @$1.2
bought 1000 on FEB 14 @$1
bought 400 on FEB 15 @$1
sold 2000 on FEB 19 @$1.5

For this, the formula/function would know that:
=>for the 300 sold on FEB 12, 100 bought on FEB 1 is depleted and 200 of the 500 bought on FEB 1 is depleted.
=>for the 100 sold on FEB 13, the 100 bought on FEB 13 is depleted
=>for the 2000 sold on FEB 19, it knows that:
==>the 100 bought on FEB 1, the 200 of the 500 bought on FEB 1, and the 100 bought on FEB 13 is depleted so it can't consider those quantities
==>it would consider the remaining 300 of the 500 bought on FEB 1, 1000 bought on FEB 10, and 700 of the 1000 bought on FEB 14

The remaining quantities that are left would be the remaining 300 of the 1000 bought on Feb 14 and the 400 bought on FEB 15.

I would like to know for each sold transaction, which quantities at which prices on which day were sold.

Thanks in advance!


Hello, I'm trying to count the number of times a name appears across multiple worksheets but I can't seem to figure it out.


I have 6 sheets to count and all the names are in column A (from A1:A100) on each sheet. The names are not in a particular order.

On sheet 7 I want to have a cell beside each persons name that counts the number of times their name appeared on the other 6 sheets.

For example Sheet 7 (called total) would have

Joe 5 (where 5 is the outcome of the formula I'm looking for to count all the times "Joe" appears on sheets 1-6).

I have it counting per sheet with =COUNTIF(A1:A130,"name") but this is not quite what I am looking for.

Thanks in advance for any advice.


Edit: I am using Excel 2007 w/ windows XP


Need a formula to calculate weeks stock in hand based on 12 months forecast.

Here is the example.

Jan2011 Feb2011 Mar2011 Apr2011 May2011 Jun2011 Jul2011 Aug2011 Sep2011 Oct2011 Nov2011 Dec2011 ----------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- Product XXXXX Actual/Forecast : 174 1674 574 574 524 524 524 524 524 728 728 728 New Purchases : 0 0 0 0 1002 0 0 0 0 0 0 0 Goods In Transit: 0 1506 0 0 0 0 0 0 0 0 0 0 Other Movements : 0 0 0 0 0 0 0 0 0 0 0 0 Closing Stock : 1270 1102 528 -46 432 -92 -616 -1140 -1664 -2392 -3120 -3848 Week Stock in Hand ? ? ? ? ? ? ? ? ? ? ? ?

So, in Month of Jan2011 the Closing stock is 1270, need a formula to calculate how many weeks will this stock cover look at future forecast.


I am trying to do a very simple copy and paste of a simple formula in Excel 2007 and nothing seems to be working. Excel will not let me copy and paste a formula and will only paste the value into to workbook. For instance, if I make a very simple spreadsheet such as:
A1 type in 10, A2 type in =A1 (calculated A2 to be 10)
B1 type in 5

And then click the copy on B1, and then click paste special on cell B2, the only options it gives me are text and unicode text and so no matter how I paste, cell b2 will always be populated with a value of 5. I cannot imagine a simpler copy and paste and no matter what I do I can't make this work.
All cells are formatted as general. It seems that all copy and pasting of formulas in my excel has been disabled. If I open any spreadsheet on my computer, I can't copy and paste formulas, but do the exact same thing on the exact same spreadsheet on any other computer and it works no problem.

Any help?


If I want to activate a workbook, I would do this:

Code:

Windows("File.xls").Activate


But what if the File.xls was a variable named File?

I have tried this:

Code:

Windows(File).Activate


Without success. Please advise.