Database inventory question

[One Eye]

Hello all,
I have been asked by my company to build an Excel spreadsheet to track inventory as it ages. Most of our users have little or no training on Excel (neither do I), so the ideal template will require only data entry. I have succeeded in building a working model, but I have run into a couple of problems that I just cannot get my head around.
First, there are a couple of simple formulas on the main inventory page. In Column C and Column F, I have formulas that I would like to apply to each new item entered into the database, but if there is no record in the column, not have it reflect a value. In other words, how do I get the formula to apply to new records without applying Fill Down and trying to guess the range? Right now, my formula in C, which refers to the function of Today, returns a value of 37000+ if the there is no value in Column A, the date received in inventory.
Next question. When an item is sold, I would like to have the sales manager be able to enter a value (say an "S") in a column on the inventory sheet that would remove the entire row from that sheet and transfer the data (not the formulas) exactly to the next sheet and remove it from the prior one. Right now, they are using cut and paste, and it is proving difficult for them.
I'd also like to add that there are some **** clever people on this board. I tried the search feature and succeeded only in feeling pretty dumb.
Thanks in advance from The Land of the Blind.

 

[Tom Urtis]

Can you answer a few questions please. The answers will help simplify the code and eliminate guesswork, plus if you are not familiar with VBA it will save you and your co-workers some effort or confusion in hopefully not needing to modify it down the road, if we can nail it correctly from the start.

(1) What are the names on the sheet tabs of the "main inventory" worksheet, and the "next sheet". Or better yet, take a moment and press Alt+F11, and in the left pane entitled "Project - VBAProject", locate your workbook name, and in the Microsoft Excel Objects folder of your workbook, see what the Sheet object number is that corresponds to your two worksheets' tab names. Example, you'd see something like
Sheet1(Main Inventory) and/or
Sheet2(Next Sheet).
What are those sheet numbers for your worksheets? If this is not clear, the sheet tab names will suffice.

(2) What is the range of columns for the data in your main inventory sheet? A:F or something more?

(3) What row does your data start in, on the main inventory sheet? Maybe row 2, with row 1 as column header names?

(4) For your first question, if for example your data is from rows 2:30, and then in A31 you enter a value, is it that, upon entering that value, that you want the formulas in row 30 to be copied into row 31, but not before a value is entered into A31?

(5) For your second question, which column is it that an "S" will be entered, that should trigger the cut and paste?

(6) When you say you want the "entire row" removed from the inventory sheet after an S is entered, do you mean from A:IV, or just from A:F if you have information from G:IV that you don't want to delete?

Sorry to bombard you with all these questions, but it'll save us some time (and web space) if we get it right from the start.

Any other useful info you want to throw in would be appreciated.

 

[One Eye]

Many thanks for your quick reply. Answers to your questions are as follows:
1. The Sheet tab names are "Inventory" and "Sold Inventory"
2. Column range is A:J
3. On the Inventory Sheet, the data begins in row 5, with row 4 being the column header names. The rows above contain only the the function TODAY, in A2, which is to be used in one of the formulas.
4. That is exactly what I am looking for.
5. As far as where the "S" would go, given that columns A through J contain data, I would guess that could go in column K?
6. All that needs to be pasted is the data in A:J.

As far as other information, the spreadsheet contains four worksheets total. The first, called the Inventory Tool, tracks the movement of inventory through 15 day increments, and shows the result of formulas only. The second and third are the inventory and sold inventory worksheets. The inventory worksheet contain only two formulas. In Column B, there is a formula that returns a numerical value for the subtraction of the date the part was taken into inventory from the absolute TODAY function referenced above. The other formula, in column J, is a simple subtractionof cost from sell price, to show the profit. The Sold Inventory worksheet contains no formulas, as the data has already been calculated. Consequently, the column headers are identical. The final worksheet, entitled Formulas, contains various COUNTIF and SUMIF functions which, based on the number returned in Inventory B and Sold Inventory B, allocates the # of units and the profit according to time in stock to the first page.
Tom, I thank you again for your assistance. An out of town emergency will necessitate my absence until 3/6/02, but I look forward to your response when I return.

 

[Tom Urtis]

OK, here's one way to do that.

First, for your Inventory sheet, right click on that sheet tab, left click on View Code, and paste this in:

'''''''''''''''''''''''''''''''''''''''''

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Column = 11 And Target.Row > 4 Then
If Target.Value = "S" Then
Target.Activate
Run "CutAndPaste"
End If
ElseIf Target.Column = 1 And Target.Row > 5 Then
Target.Activate
Application.ScreenUpdating = False
Range(ActiveCell.Offset(-1, 1), ActiveCell.Offset(-1, 9)).Copy
ActiveCell.Offset(0, 1).PasteSpecial (xlPasteFormulas)
Application.CutCopyMode = False
ActiveCell.Select
Application.ScreenUpdating = True
Else
Exit Sub
End If
End Sub

''''''''''''''''''''''''''''''''''''''''

Then, while you are still in that Visual Basic Editor screen, click on the Insert menu option (found at the top of the screen), select "Module", and paste this in:

'''''''''''''''''''''''''''''''''''''''''''

Sub CutAndPaste()

Application.ScreenUpdating = False

Sheets("Inventory").Range(ActiveCell.Offset(0, -1), ActiveCell.Offset(, -10)).Copy
Sheets("Sold Inventory").Range("A65536").End(xlUp).Offset(1, 0).PasteSpecial (xlPasteValues)

ActiveCell.EntireRow.Delete Shift:=xlUp

Application.ScreenUpdating = True

End Sub

'''''''''''''''''''''''''''''''''''''

Hit Alt+Q (or the X in the upper right corner) to exit the VBE, and you are back onto your worksheet and good to go.

Now, when a value is entered in column A beginning in row 6, the formulas from the row above will be copied to that current active row, among columns B:J.

When an "S" (case sensitive) is entered into column K beginning in row 5, that row will be deleted and the values from A:J of that row will be copied to the next available row in the Sold Inventory sheet.

One suggestion, save your current workbook as a guinea pig name and test this code with that workbook, just to be sure the code does what you want, before any of your data is altered from this code.

If I missed something, let me know.

Good luck.

 

[One Eye]

First of all, many thanks for your response. I am frankly very impressed. The program works beautifully, and is very easy to use, which is exactly what I (and my co-workers) need. The only hiccup seems to be when the information is copied from one row to the next, it copies not only the formulas, but all information - the stock #, manufacturer, etc. This is certainly not a problem, as the manager entering data can simply overwrite it, but I was wondering if there was a way that only the formulas in columns B and J might be copied, leaving the others blank.

Again, Tom, I thank you for your timely and excellent help with this.