Redistributing Cells When Inventory Runs Out...
Oct 5, 2009
I have a bunch of geographic "buckets" (17) that hold inventory. What I need to do is carefully track the inventory of each area. If at any point, this inventory runs out, there is still a possibility that some could be sold from this bucket. So, what I need to do is redistribute the volume that is sold from the sold-out bucket to all of the other buckets.
This is not linear -- i.e., any of the buckets could be out at any time so the solution is very fluid/dynamic.
Example: ...
View 9 Replies
ADVERTISEMENT
Sep 10, 2012
How do I print multiple inventory labels based on our inventory levels?
For example:
Item No 1000
Descr Window
Customer Taylor
PO 9001
Quantity 10
Item No 1010
Descr Door
Customer Jones
PO 9011
Quantity 35
I want to print 10 labels with the info from Item No 1000 and 35 labels from Item No 1010????
View 8 Replies
View Related
Aug 18, 2009
I had been trying really hard to get some solution on COGS valuation & inventory valuation on FIFO basis. my daily transactions are typical sales & purchases.
Attached is the inventory in/out movement from Quick Books. in the column "Num" type bill is the entry from purchase bill whihc always has a reference as P/O####. This is how i will capture the landed cost against a PO., another type "Inv Adj" is inter warehouse transfer. Name is cusotmer, Inventory is my item number. in some cases it is like "2000", in some cases it is 10000:10121, and in some cases it is 10000:10200:10201 that is why they fall in different columns when i export them.
What I would lilke to do: 1) Run a report by month, by customer showing cost of goods sold on FIFO basis, I can capture sales amount by running another report.
View 11 Replies
View Related
Aug 8, 2006
I am not an excel whiz, so I will need layman's terms if possible. I may have found a solution to my problem under another thread, but I couldn't understand it. I have only briefly worked with macros, a very long time ago. Here goes...
I have an inventory list that has not been updated for sales of the inventory, only for purchases. I have sales lists, generally by month, in separate spreadsheets. I am looking for a simple, efficient way of either eliminating or at least matching up the data in the sales list to the inventory list in order to remove sold items from inventory (on paper). I am hoping the result will be a fairly accurate inventory list, and related value, so that a physical inventory count won't be necessary at this time. Obviously I could copy all of the sales info into one spreadsheet, sort by inventory number, then manually delete all sold items from the inventory listing. I am REALLY hoping there is a better option.
View 6 Replies
View Related
Feb 27, 2007
I have a workbook. The second tab is inventory numbers and the first tab has my inventory items. I scan in the inventory number (unique) and it adds it to my inventory sheet. As I use inventory I scan the barcode and it inputs the serial number into my used inventory tab. What I need to do it when I scan the serial number for used inventory into the used inventory sheet to have it go to the inventory sheet and delete the line for that serial number.
View 9 Replies
View Related
Jun 18, 2014
So I have a worksheet that has a lot of macros in it. I have just barely run into a problem that happens anytime a change a cell in another workbook that I have open. The second workbook I have open isn't even a macro enabled workbook. But for some reason it gives me an error and says I am trying to run this code.
[Code] ....
It gives me the runtime error 9. This code is supposed to run when I click on a combobox in my main workbook but it is running anytime I change something in my other simple workbook. Why this happens and how to stop it? I have had a lot of similar problems in the past but I just dealt with it by only having one workbook open but in this case I will need both.
View 2 Replies
View Related
Aug 2, 2008
So i don't have any events that i know of that should trigger my macros to run. But everytime i delete a cell, or hide rows, it seems all my code/macros just auto run. And i had a few times when i hid the rows, the code would start, and then my excel application would crash.
View 4 Replies
View Related
Dec 31, 2013
I wrote this VBA for a particular sheet:
Private Sub Worksheet_Change(ByVal Target As Range)
If Range("H5") = "Y" Then
Columns("L").EntireColumn.Hidden = True
Else
Columns("L").EntireColumn.Hidden = False
End If
End Sub
If the value of H5 is "Y" it hides column "L". It was working fine at one time. Then I wrote a bunch of UDF's at the workbook module level. I then went back it use the "hide" function and it didn't work.
I created a couple new worksheets in the same workbook, and the same VBA works fine on them. I created a copy of the "non-working" worksheet and added the VBA to the copied sheet and it fails to work.
If I create an error in the VBA (like: Column("L").EntireColumn.Hidden = True), I get an error when I change the (non-working) worksheet - so I know it is "trying to run", at least sort of.
View 6 Replies
View Related
Jun 9, 2009
I have written a number of UDFs, which work fine in my spreadsheet. Two of these are more complex, and I'm having a few problems...
When I run any VBA routine (i.e. control passed to VB), these two UDFs return #VALUE! errors, but I can't see why. As these are Functions, I can't think of a way to test where the error is being generated, like I would in a Sub
When I return to Excel, any recalculation fixes the problem, which makes me think something is declared incorrectly. All named cell references relate to other VBA functions, calling the relevant row / column numbers, and I think these are all correct. I've tried removing AS INTEGER etc, to avoid type mismatches, and adding APPLICATION in front of worksheetfunction...
Function precedentStart(Target As Range) As Integer
Application.Volatile
With ThisWorkbook.Sheets("sheet1")
Dim splitPrecedent As Variant, lookupI As Integer, rowI As Integer
precedentStart = .Cells(Target.Row, colOrigStart).Value
splitPrecedent = Split(Target.Text, ",")
For i = LBound(splitPrecedent) To UBound(splitPrecedent)
rowI = WorksheetFunction.Match(splitPrecedent(i), .Range("schedRefs"), 0)
lookupI = .Cells(rowI, colAdjEnd).Value
If lookupI > precedentStart Then precedentStart = lookupI
Next i
End With
End Function
View 9 Replies
View Related
Nov 28, 2009
I can't seem to find an answer for this, but as an alternative, on Worksheet Activate the code runs and places a 1 in Range("A1") and therefore the next time the macro will not run.
This works fine for me, but just thinking if somebody who doesn't know why the one is in A1 and deletes it this macro will run again on sheet activate.
Private Sub Worksheet_Activate()
Dim LR As Long
Dim rng As Range
If Sheets("Charts").Range("A1").Value = 1 Then Exit Sub
LR = Sheets("POD").Cells(Rows.Count, "B").End(xlUp).Row
With Sheets("Charts")
.Range("I129").FormulaR1C1 = "=ROWS(R1C1:R[-128]C[-8])"
Set rng = .Range("A129").Resize(, 10)
rng.Copy rng.Resize(LR - 4)...............
View 9 Replies
View Related
Jan 28, 2008
Firstly i am running Excel 2003 (at work)
Now my problem is that i have been asked to make a spreadsheet about Printer and Fax Cartridges (Zzzzz) but i do not know how to do the sum.
this is how my Spreadsheet looks like this (cartidge amount etc all on top line of spreadsheet)
(Hp45 etc all down left hand side) ...
View 9 Replies
View Related
Feb 9, 2010
I AM TRYING TO SUM OF EACH DAILY INVENTORY ITEM. PREVIOUSLY I USED FORMULA SUGGESTED FROM TEETHLESSMAMA (=SUMPRODUCT(--($A$5:$J$13=A19),$B$5:$K$13)).
BUT THIS FORMULA NOT WORK FOR NEW FORMAT OF INVENTROY DATA. I tried to make some change in it to get the result, which is not working well.
View 9 Replies
View Related
Jul 3, 2009
I am doing inventory right now and need help sorting numbers that I have. Ex:
I have numbers like
10
1001
101
AB1394-10
AB2948-MPG
XD89-MPG KIT
F64 KIT
GSW294-HD10
I need something that will help me sort it by the numbers only (numerically by the first part only). I want the above numbers to look like the following after sorting:
10
F64 KIT
XD89-MPG KIT
101
GSW294-HD10
1001
AB1394-10
AB2948-MPG
Basically, I'm trying to get the computer to sort the numbers numerically by disregarding any letter before the number.
View 7 Replies
View Related
Feb 12, 2007
I have a worksheet that has multiple units listed on it. Each unit has a materials used list with quantities. I want to reference this column to another sheet and get a total materials needed list and also reference it to my inventory and subtract that number from my materials on hand. how can I do this?
View 6 Replies
View Related
Mar 7, 2007
I am trying to figure out a formula for doing my inventory. I have items with control numbers on them and I would like to determine how many items there are. Example Starting #12335A Ending #12445A, if you subtract 12445A from 12345A you get 10, but in the inventory you need to count the ending number as it still remains in your count, so the answer is actually 11. I have been using for example =Sum(A8,-A9) +1 to get the correct number. However, if I have additional lines on my report that do not have inventory in them, they will show an incorrect total of 1 because of the +1 in the formula. I am trying to figure out how to do this calculation without having to delete and add the formula each time inventory is added. I have people that will not go near the formulas, so I need it to total with out having to be changed.
View 8 Replies
View Related
Apr 17, 2009
I am trying to put together an inventory worksheet and I have a rec'd column and a usage column, followed by a total column. I want to be able to havea formula in the total column at all times, so whoever updates the inventory sheet just needs to put rec'd and used. What I am trying to do though, is have the total row for a particular month blank, unless someone enters a rec'd amount or a used amount.
View 9 Replies
View Related
Jan 29, 2010
So far I was able to find solutions to all of my small problems with excel just by searching here but with this one I need help and ideas how can it be done differently.
We need to track inventory from when its taken, when it was installed and we need to know every month when inventory is counted how much do we have on hand in our truck....
View 7 Replies
View Related
Dec 12, 2008
I created this spreadsheet to assist with pricing for my towing company. I struggled with getting the dropdown lists and combo boxes to work, and I created and deleted many of them before I got it to work like I wanted, but I think I left a mess. It runs slow, showing a delay when I click on different radio buttons, and there is an exclamation point in the Excel icon in the file directory. Could someone take a look in the code structure and tell me if there are any opportunities to clean it up. RateCalcApp.xlsm
View 2 Replies
View Related
Apr 3, 2009
In VBA I use this command, which runs *.vbs file that is used as a message box:
View 3 Replies
View Related
Jun 10, 2014
I have a macro which filters data on one sheet and copies that data onto 34 new sheets . This works perfectly until the 25th pass when Excel advises that there is insufficient memory to continue. I assumed that this was connected to the clipboard and its ability to remember 24 chunks of data so I turned it off but still Excel gave up at the 25th pass. Is there a way that I can clear the clipboard during the macro's execution or is there something else going on?
View 7 Replies
View Related
Dec 23, 2006
why the On Error GoTo statement only executes the first time an error occurs in the following
On Error GoTo CubeNotFound
For x = 2 To TotalRecords
Cells.Find(What:=WSID, After:=ActiveCell, LookIn:=xlFormulas, LookAt:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False, SearchFormat:=False).Activate
'Other actions are located here, but have been removed. These actions take place if the Find is succesful.
CubeNotFound:
Message = MsgBox("WS ID " & WSID & " was not found! Excel will continue with the next WS ID.", vbOKOnly, "WS ID NOT FOUND")
Next x
I have that statement there because if WS ID is not found then VB generates an error that the user won't be able to interpret, and also stops the code. I want VB to continue to the next record if one is not found.
View 9 Replies
View Related
Feb 5, 2008
Running Windows XP; Excel 2003
I have a fairly simply macro.
It copies certain sheets out of a workbook (for a new workbook)
Unprotect the sheets in the new workbook
Deletes a particular cell on each sheet
Unhides a sheet
Sets all of the new worksheets to normal view
Protects the new sheets
Lets the user save and name the new workbook.
All is working fine Except it will not change the new workbook from Page Break View to Normal view.
The code I'm using = Sheets("name").Activate
Activewindow.View=xlNormal
Here's the strangest part. When I run the macro from a command button, the newly created workbook retains the page break view (as is in the original).
When I run the macro line by line via F8, IT WORKS JUST FINE
View 9 Replies
View Related
Feb 15, 2008
I have created a macro in xl2000 which was working fine - although through corporate upgrades my machine has now been upgraded with xl2003 and the macro runs for a bit and then crashes on the code below, although it only highlights teh 'rnum'
rnum = ActiveCell.Row
Is there a difference between xl2000 & xl2003?
View 9 Replies
View Related
Nov 13, 2008
The code below runs on a spreadsheet that has approx 600 rows which INDEX and MATCH another spreadsheet which has approx 600 rows. I takes about 4 mins to run.
Sub QC()
Range("T1").Select
ActiveCell.FormulaR1C1 = "Compared CCCD"
Range("V4").Select
Columns("T:T").ColumnWidth = 15.29
Range("S1").Select
Selection.Copy
Range("T1").Select
Selection.PasteSpecial Paste:=xlPasteFormats, Operation:=xlNone, _
SkipBlanks:=False, Transpose:=False
Application.CutCopyMode = False
Columns("T:T").Select...................
View 9 Replies
View Related
Jun 30, 2009
I want to display a progress bar for when those macros are run. I've read many different ways to do this when I search it on google but I would prefer to use the cleanest method where I can simply call the appropriate procedure for each different macro, without changing any of the other code of the progress bar.
View 9 Replies
View Related
Aug 12, 2009
I wrote some code that runs with no problem on my computer as well as another coworkers computer, but will not work on a 2nd coworker's computer. The "subscript out of range" error shows up at the point when the EMInput worksheet in the EMInput workbook is copied to the new workbook called "Out of Plane Section Cuts" (SEE BELOW). Is there an option in EXCEL or somewhere that could affect this. I don't see why you would get a subscript out of range error since the EMInput workbook is open....
View 9 Replies
View Related
Feb 16, 2007
I've a problem with the below code.
Option Explicit
Private Sub Workbook_Open()
Dim x As Date
x = InputBox("Enter End Date!")
Range("B2") = x
With Application. CommandBars("File")
.Controls("Save").Enabled = False
.Controls("Save").Visible = False
.Controls("Save As...").Enabled = False
.Controls("Save As...").Visible = False
End With
With Application.CommandBars("Standard")
.Controls("Save").Enabled = False .................
View 9 Replies
View Related
Aug 4, 2007
I have an Excel workbook which runs a query via DAO against an Access database. The query usually takes about 6-7 seconds. Id like to do some animation while the query is running. My animation is just a simple Autoshape that I rotate (1 degree at a time) in a loop. Is there a way I can excecute my query and then continue to run my code while the query is excecuting?
View 2 Replies
View Related
Nov 5, 2009
I'm trying to set up an inventory spreadsheet for my work, but i'm running into a snag.
What I want to do is have three columns that interact with eachother.
1. Units in stock
2. Reorder Level
3. Reorder Tolerance
4. Status
What I want, is to have the status column react in one of three ways. Either "In Stock", "Reorder Needed" and "Out of Stock".
So if units in stock is at 110, with a reorder level of 100, and a tolerance of 10%, I want the Status to show "Reorder Needed" with a red cell color. But if stock is at 111 or more, I want it to be shown as "In Stock". Zero should be "Out of Stock".
View 6 Replies
View Related
Nov 30, 2009
I have inventory on an excel sheet with over 1500+ items. There are number of columns including: product id, product code, description, price, headline, path etc. I am uploading this to my online store I am creating. And, I have a lot of duplicate items so I can place them in different sections of the store by editing their path. However, these duplicate items in the excel sheet only have the product id, and not the description, price, and all the other cells. Thus, I need a forumla that will find matching product id's and fill in blank cells with their description, price, etc. so it will match its partner completely .......
View 14 Replies
View Related
