#1
|
|||
|
|||
Inventory: formula to show parts to order.
See attached with notes, Melvin1942 |
#2
|
|||
|
|||
Formula for inventory
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 |
#3
|
|||
|
|||
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 |
#4
|
|||
|
|||
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 |
#5
|
|||
|
|||
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. |
#6
|
|||
|
|||
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 |
#7
|
|||
|
|||
Quote:
Quote:
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:
Col D ? |
#8
|
|||
|
|||
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.
|
#9
|
|||
|
|||
.
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. |
#10
|
|||
|
|||
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
|
#11
|
|||
|
|||
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. |
#12
|
|||
|
|||
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 |
#13
|
|||
|
|||
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 ? |
#14
|
|||
|
|||
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.
|
#15
|
|||
|
|||
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. |
Thread Tools | |
Display Modes | |
|
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 |