Microsoft Office Forums

Go Back   Microsoft Office Forums > >

Reply
 
Thread Tools Display Modes
  #1  
Old 04-21-2018, 10:45 AM
melvin1942 melvin1942 is offline Inventory: formula to show parts to order. Windows 10 Inventory: formula to show parts to order. Office 2007
Advanced Beginner
Inventory: formula to show parts to order.
 
Join Date: Apr 2016
Posts: 42
melvin1942 is on a distinguished road
Default Inventory: formula to show parts to order.


See attached with notes,

Melvin1942
Attached Files
File Type: xlsx Inventory.xlsx (10.8 KB, 15 views)
Reply With Quote
  #2  
Old 04-22-2018, 10:54 AM
melvin1942 melvin1942 is offline Inventory: formula to show parts to order. Windows 10 Inventory: formula to show parts to order. Office 2007
Advanced Beginner
Inventory: formula to show parts to order.
 
Join Date: Apr 2016
Posts: 42
melvin1942 is on a distinguished road
Default 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
Attached Files
File Type: xlsx Inventory.xlsx (10.8 KB, 10 views)
Reply With Quote
  #3  
Old 04-22-2018, 10:58 AM
Logit Logit is offline Inventory: formula to show parts to order. Windows 10 Inventory: formula to show parts to order. Office 2007
Expert
 
Join Date: Jan 2017
Posts: 529
Logit is a jewel in the roughLogit is a jewel in the roughLogit is a jewel in the rough
Default

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
Attached Files
File Type: xlsm Inventory.xlsm (21.3 KB, 17 views)
Reply With Quote
  #4  
Old 04-22-2018, 11:26 AM
melvin1942 melvin1942 is offline Inventory: formula to show parts to order. Windows 10 Inventory: formula to show parts to order. Office 2007
Advanced Beginner
Inventory: formula to show parts to order.
 
Join Date: Apr 2016
Posts: 42
melvin1942 is on a distinguished road
Default

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
Reply With Quote
  #5  
Old 04-22-2018, 02:41 PM
Logit Logit is offline Inventory: formula to show parts to order. Windows 10 Inventory: formula to show parts to order. Office 2007
Expert
 
Join Date: Jan 2017
Posts: 529
Logit is a jewel in the roughLogit is a jewel in the roughLogit is a jewel in the rough
Default

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.
Reply With Quote
  #6  
Old 04-24-2018, 10:45 AM
melvin1942 melvin1942 is offline Inventory: formula to show parts to order. Windows 10 Inventory: formula to show parts to order. Office 2007
Advanced Beginner
Inventory: formula to show parts to order.
 
Join Date: Apr 2016
Posts: 42
melvin1942 is on a distinguished road
Default

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
Reply With Quote
  #7  
Old 04-24-2018, 12:09 PM
Logit Logit is offline Inventory: formula to show parts to order. Windows 10 Inventory: formula to show parts to order. Office 2007
Expert
 
Join Date: Jan 2017
Posts: 529
Logit is a jewel in the roughLogit is a jewel in the roughLogit is a jewel in the rough
Default

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 ?
Reply With Quote
  #8  
Old 04-24-2018, 07:24 PM
melvin1942 melvin1942 is offline Inventory: formula to show parts to order. Windows 10 Inventory: formula to show parts to order. Office 2007
Advanced Beginner
Inventory: formula to show parts to order.
 
Join Date: Apr 2016
Posts: 42
melvin1942 is on a distinguished road
Default

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.
Reply With Quote
  #9  
Old 04-24-2018, 08:05 PM
Logit Logit is offline Inventory: formula to show parts to order. Windows 10 Inventory: formula to show parts to order. Office 2007
Expert
 
Join Date: Jan 2017
Posts: 529
Logit is a jewel in the roughLogit is a jewel in the roughLogit is a jewel in the rough
Default

.


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.
Attached Images
File Type: jpg Inventory.jpg (90.5 KB, 30 views)
Attached Files
File Type: xlsm Inventory.xlsm (21.2 KB, 7 views)
Reply With Quote
  #10  
Old 04-27-2018, 11:07 AM
melvin1942 melvin1942 is offline Inventory: formula to show parts to order. Windows 10 Inventory: formula to show parts to order. Office 2007
Advanced Beginner
Inventory: formula to show parts to order.
 
Join Date: Apr 2016
Posts: 42
melvin1942 is on a distinguished road
Default

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
Reply With Quote
  #11  
Old 04-27-2018, 11:28 AM
Logit Logit is offline Inventory: formula to show parts to order. Windows 10 Inventory: formula to show parts to order. Office 2007
Expert
 
Join Date: Jan 2017
Posts: 529
Logit is a jewel in the roughLogit is a jewel in the roughLogit is a jewel in the rough
Default

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.
Reply With Quote
  #12  
Old 04-27-2018, 12:05 PM
melvin1942 melvin1942 is offline Inventory: formula to show parts to order. Windows 10 Inventory: formula to show parts to order. Office 2007
Advanced Beginner
Inventory: formula to show parts to order.
 
Join Date: Apr 2016
Posts: 42
melvin1942 is on a distinguished road
Default

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
Reply With Quote
  #13  
Old 04-27-2018, 02:55 PM
Logit Logit is offline Inventory: formula to show parts to order. Windows 10 Inventory: formula to show parts to order. Office 2007
Expert
 
Join Date: Jan 2017
Posts: 529
Logit is a jewel in the roughLogit is a jewel in the roughLogit is a jewel in the rough
Default

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 ?
Reply With Quote
  #14  
Old 04-27-2018, 07:33 PM
melvin1942 melvin1942 is offline Inventory: formula to show parts to order. Windows 10 Inventory: formula to show parts to order. Office 2007
Advanced Beginner
Inventory: formula to show parts to order.
 
Join Date: Apr 2016
Posts: 42
melvin1942 is on a distinguished road
Default

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.
Reply With Quote
  #15  
Old 04-30-2018, 04:06 AM
ArviLaanemets ArviLaanemets is offline Inventory: formula to show parts to order. Windows 8 Inventory: formula to show parts to order. Office 2016
Expert
 
Join Date: May 2017
Posts: 869
ArviLaanemets has much to be proud ofArviLaanemets has much to be proud ofArviLaanemets has much to be proud ofArviLaanemets has much to be proud ofArviLaanemets has much to be proud ofArviLaanemets has much to be proud ofArviLaanemets has much to be proud ofArviLaanemets has much to be proud ofArviLaanemets has much to be proud of
Default

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.
Attached Files
File Type: xlsx Inventory.xlsx (22.1 KB, 14 views)
Reply With Quote
Reply

Thread Tools
Display Modes


Similar Threads
Thread Thread Starter Forum Replies Last Post
Inventory: formula to show parts to order. 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
Inventory: formula to show parts to order. add the functionality to show & hide paragraphs, parts of tables, etc pgwolfe Word 3 09-24-2013 07:58 PM

Other Forums: Access Forums

All times are GMT -7. The time now is 03:40 AM.


Powered by vBulletin® Version 3.8.11
Copyright ©2000 - 2024, vBulletin Solutions Inc.
Search Engine Optimisation provided by DragonByte SEO (Lite) - vBulletin Mods & Addons Copyright © 2024 DragonByte Technologies Ltd.
MSOfficeForums.com is not affiliated with Microsoft