Inventory: formula to show parts to order.

melvin1942 · #1 04-21-2018, 10:45 AM

See attached with notes,

Melvin1942

melvin1942 · #2 04-22-2018, 10:54 AM

I am needing a formula to calculate the qty to order in column I. And I would like to take column E and column I and show the PART NUMBER and QTY TO ORDER on sheet 2. I would also like to show them in columns A and B on sheet 2 and put them in the first empty cells so they could be printed as an PART ORDER if it is possible.

Thank you for your help.

Melvin1942

Logit · #3 04-22-2018, 10:58 AM

Paste in a routine module :

Code:

Option Explicit

Sub Copy_Columns()
Dim shA As Worksheet, shB As Worksheet, LastRow As Long
    
    Set shA = Sheet1
    Set shB = Sheet2
    
    LastRow = shA.Cells.Find("*", , xlValues, , 1, 2).Row
    
    Application.ScreenUpdating = False
    
    shA.Activate
          
    Application.CutCopyMode = False
    
    shA.Range("E5:E" & LastRow).Copy shB.Range("A3")
    shA.Range("I5:I" & LastRow).Copy shB.Range("D3")
       
    Application.ScreenUpdating = True
    shB.Activate
End Sub

Sub Reset()
Dim shA As Worksheet, shB As Worksheet, LastRow As Long

    Set shA = Sheet1
    Set shB = Sheet2
    LastRow = shB.Cells.Find("*", , xlValues, , 1, 2).Row
    
    shB.Range("A3:D" & LastRow).Clear
    shA.Activate

End Sub

melvin1942 · #4 04-22-2018, 11:26 AM

Logit, I am new at this, what do you mean by Paste in a routine module. How do I do that and where do I put it. Do I put it in sheet1 or sheet2? Could you do it in the excel file that I sent and sent back to me in a email. If you can my email address is leroy9804@aol.com. Thank you for your help.

Melvin1942

Logit · #5 04-22-2018, 02:41 PM

Download the attached workbook "Inventory" via the link below the code in Post #3 (my last post).

You can view the code in the VBE. The workbook is already setup to use the code.

melvin1942 · #6 04-24-2018, 10:45 AM

Logit I down loaded, click on create order and on Sheet two I got #REF! so I do not know if I done something wrong. What is the VBE? I do not know if it moved the part numbers to sheet 2 or if they were already there. I need it to move the part number that i need to order into sheet 2 also. I would like for it to not have amy spaces between the part numbers in sheet 2 and the other spaces to be blank. Thank your for the time you have put into this.

Melvin1942

Logit · #7 04-24-2018, 12:09 PM

Quote:

I got #REF!

If you did not change anything in the download workbook, you should not be receiving any errors. It works as intended here.

Quote:

What is the VBE?

In the download workbook, right click on Sheet1 tab, then select VIEW CODE.
What you see now is the VBE (Visual Basic Editor). When you are coding in VBA
(Visual Basic for Applications), which is what Excel runs on, the VBE is where you
place your code.

While in the VBE, double click on MODULE 1, on the left side of the screen. If all you see at first is MODULE (in the treeview display), double click on MODULE, then on MODULE 1.

When you double click on MODULE 1, on the right side of the screen appears the macro code that runs your ordering. In this particular project, this is all the code required to run your project. In other projects, you may find alot more code in different locations in the VBE. Depends on the need of the project.

Quote:

the other spaces to be blank

Do you mean on Sheet2 you want QUANTITY TO ORDER to display in Col B rather than
Col D ?

melvin1942 · #8 04-24-2018, 07:24 PM

Right clicked on Sheet 1 and clicked on view code. I get a box with the file name on top. In the box I get Option Exlpicit. Then I get another box with the code. I do not see anythig about modules.

Logit · #9 04-24-2018, 08:05 PM

.

When you get into the VBE you should see the layout as in the image below. Here is a download link if you cant see it : https://www.amazon.com/clouddrive/sh...DAWtCF7b01zg98

The treeview on the left side will let you access the Sheet Level Modules and the Routine Modules (if any have been added to a project). There are always Sheet Level modules, even if they are not utilized.

If the treeview is not fully expanded, you can click on the small plus [+] symbols on the left side to do so.

Double clicking on the word MODULE1 will open the right side screen to the code used
in your project.

It is common practice on the Excel forums to not only provide the formula or code that is needed to answer a question but oftentimes to include the sample project with the actual code. That way the questioner has the added bonus of seeing where the code was pasted.

Hope this helps.

melvin1942 · #10 04-27-2018, 11:07 AM

Loglit, I re down loaded the file to make sure nothing had charged. Sheet 1: shows part numbers in column E5 thru E 11 and Qty to order in I5 thru I 11. In Colnmu I it show qty, in cells 5 thru 11 it should not show anything in cells I 6 thru I 11 as there is nothing in Cells C6 and 11 and G6 thru 11 . Also shows J and K 5 shows create order. When I open this file is there anything I need to do on sheet !. Do I need to click on the Create order for it to put anything.in sheet 2? Sheet 2 does not show anything in column A5 thru 11 and

Logit · #11 04-27-2018, 11:28 AM

Do you have a standard PAR VALUE for Col G "Qty To Keep" ? If so, please advise what those numbers are.

The numbers in Col I were there just for testing purposes. You can delete those numbers if you want.

Ultimately, once you have the table on Sheet1 (A5:I11) finalized with amounts, you'll be able to click the CREATE ORDER button and all will be transferred to Sheet2.

melvin1942 · #12 04-27-2018, 12:05 PM

Logit Column G could be any number. G is what I want to keep on in stock. C is what I have on in stock and I is what I need to order to get my stock back to where I need it.
Hope I explained it right.

Melvin1942

Logit · #13 04-27-2018, 02:55 PM

Yup, got that. Col G is your PAR value.

What are the numbers for those rows ?

Also, how many parts numbers do you have and what are their PAR levels ?

melvin1942 · #14 04-27-2018, 07:33 PM

I thank you for your help, but you are going to have to help me. I do not understand what you are asking. For instants you ask what are the numbers for those rows and how many par numbers do you have and what are their PAR levels. As far as how many part I do not know at this time. There could be as many as 500 or more. As I said before I need help.

ArviLaanemets · #15 04-30-2018, 04:06 AM

An alternative without coding - you can do all this with formulas only.

I left all your sheets as they were and added new ones.

You can keep several inventories. I added QtyPurchased field so user can follow, were ordered parts received or not.

The print area definition for PartOrder sheet is based on Dynamic Name, so you can have any number of report rows prepared, but only rows not empty are printed.

In case you aren't familiar with defined Tables, to add a new row into table:
1. Select right bottom cell of Table, and press Tab;
2. Select any cell in Table, activate Design menu, and at left of header click Resize Table - set the number of rows you want in Table;
3. Enter something into next row at bottom of table (avoid columns with formulas, and columns with Data Validation for better too).

When you add a new row(s) into Table, Data Validations, Conditional Formatting, and formulas (latter in case the whole column has same formula) are copied into new row(s) automatically.

Some columns are hidden, as they are there to keep data needed for calculations and user don't need to see them. You can unhide them to to understand, how all works, of-course.

Similar Threads
Thread	Thread Starter	Forum	Replies	Last Post
Formula/VBA to identify order of entry dates	lesmielles	Excel Programming	3	02-08-2017 06:14 AM
Timeline creation - show events out of order	triallawyer	PowerPoint	0	03-17-2015 08:48 PM
Changing order of animations in slide show mode	mrbutler88	PowerPoint	1	02-14-2015 11:57 PM
Show & hide paragraphs, parts of tables, etc	Preloader	Word	2	10-19-2013 02:37 PM
add the functionality to show & hide paragraphs, parts of tables, etc	pgwolfe	Word	3	09-24-2013 07:58 PM