stock control

[stiv]

I have an inventory list online that is constantly updated. What I'm
looking to do is check and see which products are out of stock and
which have been added into stock this process happens twice a week. To
figure this out maybe would be a macro that could look at Column C
(items stock level) look and see any item with a 0 (out of stock) would
then take the mfr code (ex. ACO-AP1650) in column A and list it in
column B the products that are out of stock.

Column A ............ Column B.........Column C

ACO-AP1650.....................................5
FIS-43REJ............FIS-43REJ.............0
SAN-G34DD......................................2

Thank you for your time,

Greg

 

[acw]

Greg

the following macro will give you what you want. However, why don't you just fill column B with a formula. Put the following in B2 and copy down as required.
Formula
=IF(C2,"",A2)

Macro
Sub aaa()
Range("c2").Select
While Not IsEmpty(ActiveCell)
If ActiveCell = 0 Then
ActiveCell.Offset(0, -1) = ActiveCell.Offset(0, -2)
Else
ActiveCell.Offset(0, -1) = ""
End If
ActiveCell.Offset(1, 0).Select
Wend
End Sub


Tony



----- stiv > wrote: -----

I have an inventory list online that is constantly updated. What I'm
looking to do is check and see which products are out of stock and
which have been added into stock this process happens twice a week. To
figure this out maybe would be a macro that could look at Column C
(items stock level) look and see any item with a 0 (out of stock) would
then take the mfr code (ex. ACO-AP1650) in column A and list it in
column B the products that are out of stock.

Column A ............ Column B.........Column C

ACO-AP1650.....................................5
FIS-43REJ............FIS-43REJ.............0
SAN-G34DD......................................2

Thank you for your time,

Greg

 

[Jim Feaver]

Hi Greg:
You can call it from Sheet1's Worksheet_Change event procedure.

'example of calling from the event proedure
Private Sub Worksheet_Change(ByVal Target As Range)
CheckStock
End Sub

'place in a standard module
Sub CheckStock()
Dim lLastRow As Long, i As Integer
Dim Sh1 As Worksheet

Set Sh1 = ThisWorkbook.Worksheets("Sheet1")

lLastRow = Sh1.Cells(Rows.Count, "A").End(xlUp).Row

Application.EnableEvents = False
For i = 1 To lLastRow
If Sh1.Range("A1").Offset(i, 2).Value = 0 Then
Sh1.Range("A1").Offset(i, 1).Value = _
Sh1.Range("A1").Offset(i, 0).Value
Else
Sh1.Range("A1").Offset(i, 1).Value = ""
End If
Next i
Application.EnableEvents = True

Set Sh1 = Nothing

End Sub

Regards,
Jim Feaver

 

[stiv]

Thank you, Tony and Jim for your help make my job easier.

Gre