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

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" <(E-Mail Removed)> wrote in message
news:32BECB61-6D56-46BB-8F07-(E-Mail Removed)...
> 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

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" <(E-Mail Removed)> wrote in message
> news:32BECB61-6D56-46BB-8F07-(E-Mail Removed)...
> > 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
>
>

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" <(E-Mail Removed)> wrote in message
news:99A2148E-C0AF-438F-A202-(E-Mail Removed)...
> 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" <(E-Mail Removed)> wrote in message
>> news:32BECB61-6D56-46BB-8F07-(E-Mail Removed)...
>> > 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
>>
>>