Checking a range

Posted by M Walker on August 08, 2001 1:47 AM

Hi,

I'm having a little trouble. I've been given a code that should enable me to check a range of cells for a value below 5. What i would like to happen is if a cell is below this value to be added to a listbox on a user form. The values represent number of stock so i would like both the number of stock left and the stock description. I'm using excel 97.

The problem i'm having is that it dosen't work, simple as that but i don't know why or how to fix it.

Here is the code i'm using...
Dim Stocklevels As Range
Set Stocklevels = Sheets("Totals").Range("a4:a336")
Dim cell As Range
Dim comparerange As Range
ListBox1.Clear
For Each cell In Stocklevels
If cell &LT; 5 Then
ListBox1.AddItem (cell)
End If
Next
End Sub

Any help would be very much appreciated.

Thanks,
Matt

Posted by Ivan F Moala on August 08, 2001 4:52 AM

I think the code you need is;

Dim Stocklevels As Range
Dim cell As Range
Dim vData() As Variant
Dim vDataCount As Double
Dim x As Double, y As Integer

Set Stocklevels = Sheets("Totals").Range("b4:b336")

ListBox1.Clear
ListBox1.ColumnCount = 2
vDataCount = Application.WorksheetFunction.CountIf(Stocklevels, "&LT;5")
ReDim vData(vDataCount - 1, 1)

x = -1

For Each cell In Stocklevels

If cell &LT;&GT; "" Then
If cell.Value &LT; 5 Then
x = x + 1
For y = 1 To 2
vData(x, 0) = cell.Offset(0, -1)
vData(x, 1) = cell
Next

End If
End If
Next

ListBox1.List = vData

Ivan

Posted by M Walker on August 08, 2001 5:33 AM

Hi,
Thanks a lot. However, there is an error when it comes to Listbox1.clear. For some reason it dosen't seem to like this command. ANy ideas?