Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Conditional Statements
I'm trying to set up Excel to run an Inventory spreadsheet for my lab, with
at least four columns: Consumable Name, Current Amount in Stock, Minimum Amount Needed in Stock, and Unit of Measure. I'd really like to have a program set up to print out on another spreadsheet what consumables are at or below the minimum amount needed as an easy-read purchasing list. In traditional programming, I'd need to define two variables, a RowNumber and a PrintToRowNumber. I'd set up a While() statement to run while there is a value in the Consumables Name column (we may have varying total number of consumables), which would then IF() to determine if a value is below its minimum and then print out each consumable testing TRUE to a unique row in the second spreadsheet, ala shopping lists. Each iteration of a print would then increase the value of PrintToRowNumber by 1, and each iteration of the entire While() loop would increase RowNumber by 1. How is this best accomplished in Excel? Is there an easier method to find values less than their designated minimum? Thanks! Dan |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Conditional Statements
Assuming Row 1 is used for a header, that the four columns you showed are
actually Columns A thru D, and that the Report will be written to another worksheet in the same workbook as the Data sheet, then give this code a try... Sub BelowMinimum() Dim C As Range Dim X As Long Dim Counter Dim LastRow As Long Dim DataSheet As Worksheet Dim ReportSheet As Worksheet Set DataSheet = Worksheets("Sheet6") Set ReportSheet = Worksheets("Sheet7") Counter = 1 With DataSheet .Rows(1).EntireRow.Copy ReportSheet.Range("A1") LastRow = .Cells(Rows.Count, "A").End(xlUp).Row For X = 2 To LastRow If .Cells(X, "B").Value < .Cells(X, "C").Value Then Counter = Counter + 1 .Rows(X).EntireRow.Copy ReportSheet.Cells(Counter, "A") End If Next End With End Sub Make sure you change the Worksheets references to the actual sheet names for your worksheets. Rick "dmaioran" wrote in message ... I'm trying to set up Excel to run an Inventory spreadsheet for my lab, with at least four columns: Consumable Name, Current Amount in Stock, Minimum Amount Needed in Stock, and Unit of Measure. I'd really like to have a program set up to print out on another spreadsheet what consumables are at or below the minimum amount needed as an easy-read purchasing list. In traditional programming, I'd need to define two variables, a RowNumber and a PrintToRowNumber. I'd set up a While() statement to run while there is a value in the Consumables Name column (we may have varying total number of consumables), which would then IF() to determine if a value is below its minimum and then print out each consumable testing TRUE to a unique row in the second spreadsheet, ala shopping lists. Each iteration of a print would then increase the value of PrintToRowNumber by 1, and each iteration of the entire While() loop would increase RowNumber by 1. How is this best accomplished in Excel? Is there an easier method to find values less than their designated minimum? Thanks! Dan |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Conditional Statements
Thanks Rick! This worked perfectly. Well, almost perfectly- since I want to
know when the items hit the minimum amount, I changed the IF to have a Not(**), and it's working like a charm. I appreciate the help! Dan "Rick Rothstein (MVP - VB)" wrote: Assuming Row 1 is used for a header, that the four columns you showed are actually Columns A thru D, and that the Report will be written to another worksheet in the same workbook as the Data sheet, then give this code a try... Sub BelowMinimum() Dim C As Range Dim X As Long Dim Counter Dim LastRow As Long Dim DataSheet As Worksheet Dim ReportSheet As Worksheet Set DataSheet = Worksheets("Sheet6") Set ReportSheet = Worksheets("Sheet7") Counter = 1 With DataSheet .Rows(1).EntireRow.Copy ReportSheet.Range("A1") LastRow = .Cells(Rows.Count, "A").End(xlUp).Row For X = 2 To LastRow If .Cells(X, "B").Value < .Cells(X, "C").Value Then Counter = Counter + 1 .Rows(X).EntireRow.Copy ReportSheet.Cells(Counter, "A") End If Next End With End Sub Make sure you change the Worksheets references to the actual sheet names for your worksheets. Rick "dmaioran" wrote in message ... I'm trying to set up Excel to run an Inventory spreadsheet for my lab, with at least four columns: Consumable Name, Current Amount in Stock, Minimum Amount Needed in Stock, and Unit of Measure. I'd really like to have a program set up to print out on another spreadsheet what consumables are at or below the minimum amount needed as an easy-read purchasing list. In traditional programming, I'd need to define two variables, a RowNumber and a PrintToRowNumber. I'd set up a While() statement to run while there is a value in the Consumables Name column (we may have varying total number of consumables), which would then IF() to determine if a value is below its minimum and then print out each consumable testing TRUE to a unique row in the second spreadsheet, ala shopping lists. Each iteration of a print would then increase the value of PrintToRowNumber by 1, and each iteration of the entire While() loop would increase RowNumber by 1. How is this best accomplished in Excel? Is there an easier method to find values less than their designated minimum? Thanks! Dan |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Conditional Statements
You are welcome. I'm sorry about the misread on your question... I'm glad
you were able to change it to what you needed so easily. Rick "dmaioran" wrote in message ... Thanks Rick! This worked perfectly. Well, almost perfectly- since I want to know when the items hit the minimum amount, I changed the IF to have a Not(**), and it's working like a charm. I appreciate the help! Dan "Rick Rothstein (MVP - VB)" wrote: Assuming Row 1 is used for a header, that the four columns you showed are actually Columns A thru D, and that the Report will be written to another worksheet in the same workbook as the Data sheet, then give this code a try... Sub BelowMinimum() Dim C As Range Dim X As Long Dim Counter Dim LastRow As Long Dim DataSheet As Worksheet Dim ReportSheet As Worksheet Set DataSheet = Worksheets("Sheet6") Set ReportSheet = Worksheets("Sheet7") Counter = 1 With DataSheet .Rows(1).EntireRow.Copy ReportSheet.Range("A1") LastRow = .Cells(Rows.Count, "A").End(xlUp).Row For X = 2 To LastRow If .Cells(X, "B").Value < .Cells(X, "C").Value Then Counter = Counter + 1 .Rows(X).EntireRow.Copy ReportSheet.Cells(Counter, "A") End If Next End With End Sub Make sure you change the Worksheets references to the actual sheet names for your worksheets. Rick "dmaioran" wrote in message ... I'm trying to set up Excel to run an Inventory spreadsheet for my lab, with at least four columns: Consumable Name, Current Amount in Stock, Minimum Amount Needed in Stock, and Unit of Measure. I'd really like to have a program set up to print out on another spreadsheet what consumables are at or below the minimum amount needed as an easy-read purchasing list. In traditional programming, I'd need to define two variables, a RowNumber and a PrintToRowNumber. I'd set up a While() statement to run while there is a value in the Consumables Name column (we may have varying total number of consumables), which would then IF() to determine if a value is below its minimum and then print out each consumable testing TRUE to a unique row in the second spreadsheet, ala shopping lists. Each iteration of a print would then increase the value of PrintToRowNumber by 1, and each iteration of the entire While() loop would increase RowNumber by 1. How is this best accomplished in Excel? Is there an easier method to find values less than their designated minimum? Thanks! Dan |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Conditional If Statements | Excel Discussion (Misc queries) | |||
Conditional If Statements | Excel Worksheet Functions | |||
Conditional statements | Excel Worksheet Functions | |||
Conditional Statements Help! | Excel Discussion (Misc queries) | |||
Conditional Statements | Excel Discussion (Misc queries) |