Robb - Reposting my question.

Posted by John on August 24, 2001 12:58 AM

Hi,

The code that you gave me works great but can i change the 5 for a larger value for a couple of ranges. As it is it would be pointless telling me that under 5 screws as they are delt with in hundreds. However, being told that i have under 5 sockets is usefull.

Basically what i want to do is use the same style of code that you gave me but check a range of cells (such as screws) with a min. value of 100 and check another range of cells (such as sockets)for a min. value of 5.

Thanks alot,

John

Posted by Robb on August 24, 2001 4:12 AM

John

Glad the code was of help. You may certainly base your If statement around a number of criteria, try this:

If (Worksheets("Totals").Cells(n,1) = "Screws" And _
Worksheets("Totals").Cells(n,2) &LT; 100) Or _
(Worksheets("Totals").Cells(n,1) = "Sockets" And _
Worksheets("Totals").Cells(n, 2) &LT; 5) Then

In this statement you are saying that you want a return if (screws AND &LT;100) is true, but you
also want a return if (sockets AND &LT;5) is true[(this)OR(that)]. It all depends on where you place parenthesis.
In this case (### AND $$$) OR (%%% AND ***)

If you couched it another way, say,

If (Worksheets("Totals").Cells(n,2) &LT; 5 OR _
Worksheets("Totals").Cells(n,2) &LT; 100) AND _
(Worksheets("Totals").Cells(n,1) = "Sockets" OR _
Worksheets("Totals").Cells(n, 1) "Nails") Then

you would return all occurences where Sockets or Nails had
low stock (&LT;5) or high stock (>100). It would probably be a bit pointless, but it's only
an example.

A bit long winded, but that's how it goes.

Any help?

Regards

Posted by John on August 24, 2001 4:58 AM

Hello again,

Thanks for your help. I have just tried this but it says that there is a syntax error. ANy idea?

ListBox1.ColumnWidths = ListBox1.Text & "2.5in;1in"
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, 1) = "screwscheck" And _
Worksheets("Totals").Cells(n, 2) &LT; 100) Or _
(Worksheets("Totals").Cells(n, 1) = "materialcheck" And _
Worksheets("Totals").Cells(n, 2) &LT; 5) or _
If (Worksheets("Totals").Cells(n, 1) = "leadscheck" And _
Worksheets("Totals").Cells(n, 2) &LT; 30) Or _
(Worksheets("Totals").Cells(n, 1) = "fusecheck" And _
Worksheets("Totals").Cells(n, 2) &LT; 2) 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

Thanks,

John

Posted by Robb on August 24, 2001 5:22 AM

John

I think you'll find you have an unwanted "If" on the leadscheck line. Try:

ListBox1.ColumnWidths = ListBox1.Text & "2.5in;1in"
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, 1) = "screwscheck" And _
Worksheets("Totals").Cells(n, 2) &LT; 100) Or _
(Worksheets("Totals").Cells(n, 1) = "materialcheck" And _
Worksheets("Totals").Cells(n, 2) &LT; 5) or _
(Worksheets("Totals").Cells(n, 1) = "leadscheck" And _
Worksheets("Totals").Cells(n, 2) &LT; 30) Or _
(Worksheets("Totals").Cells(n, 1) = "fusecheck" And _
Worksheets("Totals").Cells(n, 2) &LT; 2) 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 that do it?

Regards

Posted by John on August 24, 2001 5:52 AM

Dave

From what you indicate, I would say what you want to do is entirely possible. How to do it, however, depend on a couple of things:

-What sort of checkboxes are you using (ActiveX or Forms)
-Do you want the initials to all appear in one cell or separate
-Do you want to copy any other of the data from the columns

I've posted my e-mail if you would like to send a copy of the worksheet.

Regards

Posted by Henry Root on August 24, 2001 7:17 AM

I'm sorry, but just can't resist trying to tidy the code up a bit (code not tested) :-

ListBox1.ColumnWidths = ListBox1.Text & "2.5in;1in"
On Error Resume Next
ListBox1.ColumnCount = 2
rw = 0
With Worksheets("Totals")
For Each r In .UsedRange.Rows
n = r.Row
If .Cells(n, 1) = "screwscheck" And _
.Cells(n, 2) &LT; 100 Or _
.Cells(n, 1) = "materialcheck" And _
.Cells(n, 2) &LT; 5 Or _
.Cells(n, 1) = "leadscheck" And _
.Cells(n, 2) &LT; 30 Or _
(Worksheets("Totals").Cells(n, 1) = "fusecheck" And _
.Cells(n, 2) &LT; 2) Then
Myentry = .Cells(n, 1)
Myentry2 = .Cells(n, 2)
ListBox1.AddItem Myentry
ListBox1.List(rw, 1) = Myentry2
rw = rw + 1
Else
End If
Next r
End With

: Hello again, : 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, 1) = "screwscheck" And _ : Worksheets("Totals").Cells(n, 2) &LT; 100) Or _ : (Worksheets("Totals").Cells(n, 1) = "materialcheck" And _ : Worksheets("Totals").Cells(n, 2) &LT; 5) or _ : If (Worksheets("Totals").Cells(n, 1) = "leadscheck" And _ : Worksheets("Totals").Cells(n, 2) &LT; 30) Or _ : (Worksheets("Totals").Cells(n, 1) = "fusecheck" And _ : Worksheets("Totals").Cells(n, 2) &LT; 2) 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