stock control

stiv76

New Member
Joined
Nov 11, 2003
Messages
14
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
 

Excel Facts

Can you AutoAverage in Excel?
There is a drop-down next to the AutoSum symbol. Open the drop-down to choose AVERAGE, COUNT, MAX, or MIN

krisco73

New Member
Joined
Mar 17, 2003
Messages
30
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.

(y)

Kris
 
Upvote 0

TryingToLearn

Well-known Member
Joined
Sep 10, 2003
Messages
730
Code:
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
 
Upvote 0

Forum statistics

Threads
1,186,916
Messages
5,960,562
Members
438,484
Latest member
supermon

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top