Conditional Statements

[dmaioran]

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

 

[Rick Rothstein \(MVP - VB\)]

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]

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\)]

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