Could somebody check out this code and tell me whats wrong?

Ignore the last one and have a look at this one please!!

Private Sub UserForm_Initialize()
On Error Resume Next
For Each r In Worksheets("Errors").UsedRange.Rows
n = r.row
If Worksheets("Totals").Cells(n, 1) = "2108" And Worksheets("Totals").Cells(n, 2) < "5" Then
Myentry = Worksheets("Totals").Cells(n, 1) & Worksheets("Totals").Cells(n, 2)
ListBox1.AddItem (Myentry)
Else
End If
Next r
End Sub

The code is surposed to place a product description in coumn 1 of a listbox and a number in the second column if the value is below 5. At the moment though i get an empty textbox. Whats wrong with it??

John

Re: Ignore the last one and have a look at this one please!!

Try 2108 and 5 (instead of "2108" and "5")

Re: Ignore the last one and have a look at this one please!!

John

That code will only return a result if ColumnA contains 2108 AND ColumnB is <5. I think the Sheet reference ("Errors") in the For Each statement may be incorrect as well.

Try it by removing the 2108 test. Also, as long as ColumnB in your worksheet contains numbers, use <5 instead of <"5" e.g.

Private Sub UserForm_Initialize()
On Error Resume Next
For Each r In Worksheets("Totals").UsedRange.Rows
n = r.row
If Worksheets("Totals").Cells(n, 2) < 5 Then
Myentry = Worksheets("Totals").Cells(n, 1) & " " & Worksheets("Totals").Cells(n, 2)
ListBox1.AddItem (Myentry)
Else
End If
Next r
End Sub

By the way, this will simply join whatever is in ColumnsA and B (may not result in exact columns in the listbox.
(The code will only run when the form loads as it is in the initialize event)

If you want it in 2 columns, try this instead:

Private Sub UserForm_Initialize()
On Error Resume Next
ListBox1.ColumnCount = 2
rw = 0
For Each r In Worksheets("Totals").UsedRange.Rows
n = r.Row
If Worksheets("Totals").Cells(n, 2) < 5 Then
Myentry = Worksheets("Totals").Cells(n, 1)
Myentry2 = Worksheets("Totals").Cells(n, 2)
ListBox1.AddItem Myentry
ListBox1.List(rw, 1) = Myentry2
rw = rw + 1
Else
End If
Next r
End Sub

Does this help?

Regards

Thats great, thanks!! A couple of simple questions....

Thanks again,

the code works really well thanks. Is it possible to have a couple of exceptions and perhaps include ranges rather than an entire column?

Ideally i don't want to be told that a drill with plant number x is running low but need to know that we're running low of wire... if you get what i mean. If i could check several ranges and add a different lower limit.

Thanks again,

John

Re: Thats great, thanks!! A couple of simple questions....

John

I'm not sure what you mean by "have exceptions" and "include ranges rather than an entire column". If you will post again I'll try on that one.

If you need to test for colA as well as ColB, write the the first line(s) of the If statement:

If (Worksheets("Totals").Cells(n,1) = "Nails" Or _
Worksheets("Totals").Cells(n,1) = "Wire" Or _
Worksheets("Totals").Cells(n,1) = "Hammers") And _
Worksheets("Totals").Cells(n, 2) < 5 Then

This would return low stock for Wire, Nails and Hammers - you may add as many as you
like. Just use Or and keep them within the parenthesis (that starts after "If" and ends
after "Hammers"

Does that help?

Regards