Difference between two columns

[commo27]

Hey everyone, I have created a pretty sophisticated parts database and one of the last things i cant figure out is outputting a list of parts that are below the Minimum Quantity.
I have a Administration Userform and i want the parts guy to be able to click a cmd button and he will then have a list of Parts that are below the minimum stock level that he can print.

Column J is actual stock level
Column L is Minimum level.

I have posted the link to my database if anyone would like to use it. In my database, you have to login into administration userform to be able to access the workbooks and vba.
To login to get to the admin screen
Username is Admin
Password is Password

Thanks for all your help.



https://www.sugarsync.com/pf/D219095_87_6866553312

 

[kpark91]

Nobody will click on that link including me.

I suggest describing your problem clearly with examples provided.

And then we can help you.

 

[commo27]

Thanks Kpark,
I think i explained it as best as possibly in my original post

Person clicks on a cmd button

Macro runs and outputs the items that are below the Minimum Qty

Column J is actual stock level
Column L is Minimum level.

So it will look at all of Column J that is below Column L on the sheet called "Parts"

Lets say we have 10 bearings in stock and our minimum level of bearings is suppose to be 15.

The bearings should show up on the report since we are 5 below the minimum level.

Our parts guy can click this once a week and use the data to order more parts and to make sure we have the right amount of parts in inventory


As for people wanting the database, Is there a preferred method of uploading for everyone?

 

[kpark91]

Oh ok. Much better.


So, you want any items below the minimum level copy + pasted into a new sheet/different sheet?

Will there be different minimum levels for each item in column L?
or is it just a constant for minimum level that is in column L? (if so, which cell is it?)

Here is a little pseudocode

Private Sub btn1_Click()
    Dim i&, LR&
    LR = Worksheets("Parts").Range("J" & Rows.count).End(xlUp).Row
    
    For i = 2 To LR
        If Range("J" & i).Value < Range("L" & i).Value Then
            'copy + paste to different worksheet
        End If
    Next i
End Sub

 

[commo27]

Kpark,

Each row will have a different minimum level and actual level
There will be about 1000 or so rows for all of our parts

Example:
Bearings might have a min level of 10 and actual of 8
Blocks might have a min level of 2 and a actual of 1
Screws might have a Min level of 100 and actual of 200

As for the output, Our parts guy will be clicking this about once a week after he does inventory. I will take your expertise on whats the best way to display the results but it should show the entire row that is below min so he can place an order for it. If it places the results on a new sheet, would he have to delete the sheet every week once he prints out the results?

 

[kpark91]

If it places the results on a new sheet, would he have to delete the sheet every week once he prints out the results?

Hi, I've gotten a better understanding of your problem now.
but I will need to verify how we could deal with the filtered data.

1) Hide data which satisfy the condition (actual >= min), showing only the parts you must order in existing sheet
Then unhide all the rows when you're exiting your workbook.

2) Copy data which do not satisfy the condition (actual >= min), showing only the parts you must order in a new sheet
Then delete the worksheet when you're exiting your workbook.


While the first method (1) has the advantage of not creating a new worksheet, it is expected that the application will be slower due to hiding and unhiding entire rows.

For second method, it'd be nice to know the whole range of your data.
For example, A2 to AAinfinite.

 

[MrNovice]

If I am understanding you correctly try this - (obviously the Column and Rows will need to be changed) If that works drag it down over all of your rows so the formula will populate for you

IF(AND(A1>100,B1<200),25,0)

 

[commo27]

Kpark,
you are the best..I think the second idea would be great...Its creates the new sheet and then deletes when closed. And the parts guy can print that new spreadsheet

I have 13 columns in the "Parts" spreadsheet.

A Tool
B Part Description
C Part number
D Supplier
E Supplier Part Number
F Unit
G Cost
H Lead Time
I Location
J Actual Stock
K Max Quantity
L Minimum Quantity
M Comments


Kpark, I tried your Pseudo code but nothing happened at all. I checked the command button name also and that is correct.

 

[kpark91]

Kpark, I tried your Pseudo code but nothing happened at all. I checked the command button name also and that is correct.

XD. It's called a pseudocode (fake-code) because it doesn't work but only presents a basic outline of the algorithm.

Anyways, I'll start coding right now

 

[kpark91]

Put this in your worksheet module

Private Sub CommandButton1_Click()
    Application.ScreenUpdating = False
    Dim i&, LR&, count&
    LR = Worksheets("Parts").Range("J" & Rows.count).End(xlUp).Row
    Set newWS = Worksheets.Add
    
    Worksheets("Parts").Range(Cells(1, 1), Cells(1, 13)).Copy newWS.Range("A1")
    count = 2
    
    For i = 2 To LR
        If Range("J" & i).Value < Range("L" & i).Value Then
            Worksheets("Parts").Range(Cells(i, 1), Cells(i, 13)).Copy newWS.Range("A" & count)
            count = count + 1
        End If
    Next i
    Application.ScreenUpdating = True
End Sub

Put this in your Workbook module

Private Sub Workbook_BeforeClose(Cancel As Boolean)
    Application.DisplayAlerts = False
    newWS.Delete
    Application.DisplayAlerts = True
End Sub

Put this at the top of your normal module (Create a module if you already don't have it)

Public newWS As Worksheet

I have tested it and it worked.