stock control

[stiv76]

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

 

[krisco73]

Greg, I don't know what kind of access you have to your online data source. ie. I don't know if you can automatically download this info into excel or if its already in excel. My first questions would be, can you copy/paste this info into an excel sheet? and if so paste it to the same place every time like cell A1 of Sheet1.

This example may leave alot to be desired for your application but may help you along.

This macro assumes:
1. you copy and paste your online data to the same place every time.
2. you cannot simply type "=IF(C2=0,A2,"")" into cell B2, then drag down to the bottom of the list.
3. a macro button is created on sheet2 for this routine.

Sub stockfinder()
'

' Sheets("Sheet1").Select
Range("A1:C8").Select "range A1:C8 must be whatever size you need it"
Selection.Copy
Sheets("Sheet2").Select
Range("A1").Select
ActiveSheet.Paste
Range("B2").Select
ActiveCell.FormulaR1C1 = "=IF(RC[1]=0,RC[-1],"""")"
Range("B2").Select
Selection.AutoFill Destination:=Range("B2:B8"), Type:=xlFillDefault
Range("B2:B8").Select

End Sub

This will fill column B with the empty stock codes.

Maybe the way to approach this would be to have this feature be a standard part of the online data source sort of like being able to run a report in Access twice a week.

hope this helps. If not, give me some more specifics to work with and I'll try again.



Kris

 

[TryingToLearn]

Sub FindNoInv()
    Dim x As Range
    Dim rw, faddress As Variant

    rw = Sheets("sheet1").Range("a65536").End(xlUp).Row
    With Sheets("sheet1").Range("C1:C" & rw)
        Set x = .find(What:=0, LookIn:=xlValues, lookat:=xlPart, MatchCase:=False)
        faddress = x.Address
        If Not x Is Nothing Then
            Do
                Sheets("sheet1").Range(x.Address).Offset(0, -1) _
                = Sheets("sheet1").Range(x.Address).Offset(0, -2)
                Set x = .FindNext(x)
            Loop While Not x Is Nothing And x.Address <> faddress
        End If
    End With
End Sub

HTH

 

[stiv76]

Thank you for your help Guys......Makes my job easier