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

How to show all formulas in Excel?
Press Ctrl+` to show all formulas. Press it again to toggle back to numbers. The grave accent is often under the tilde on US keyboards.

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
 

TryingToLearn

Well-known Member
Joined
Sep 10, 2003
Messages
723
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
 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,152,096
Messages
5,768,058
Members
425,451
Latest member
JohnBrooksBiddle

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