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"
