MrExcel Publishing
Your One Stop for Excel Tips & Solutions

Comparing a range


Posted by M Walker on August 01, 2001 2:50 AM

Hi,

I'm trying to check a range of values for values under 5. It is intended to keep track of stock so ideally if the level falls below 5 i would like the product description to be placed in a textbox on a userform. As it is i'm stuck. I don't understand VBA very well and could do with some serious help. I was given this code by Ivan

Set Stocklevels = Sheets("Totals").Range("b5:b337")
dim cell as range
dim comparerange as range

if cell < 5 then
listbox1.additem(cell)
endif
next

But i don't understand what is actually being coded here so i can't fix the error message that appears when i run the macro.

ANy help would be appreciated.

Thanks, Matt


Posted by cpod on August 01, 2001 7:14 AM

Try this:

Dim c As Range
For Each c In Sheets("Totals").Range("b5:b337")
If c < 5 Then
UserForm1.ListBox1.AddItem (c.Offset(0, -1))
End If
Next
End Sub

This assumes that:

1 The in stock amount is in B5:B337 on a worksheet called "Totals"

2 The part description is in the same row in Column A

3 The form is called "Userform1

4 The listbox is called "Listbox1"

Posted by cpod on August 01, 2001 7:31 AM

Try this:

Dim c As Range
For Each c In Sheets("Totals").Range("b5:b337")
If c < 5 Then
UserForm1.ListBox1.AddItem (c.Offset(0, -1))
End If
Next
End Sub

This assumes that:

1 The stock amounts are in B5:B337 in a worksheet named "totals"

2 The part descriptions are in A5:A337

3 The form is named "Userform1"

4 The listbox is called "Listbox1"