Listbox1.clear error
Posted by M Walker on August 08, 2001 6:44 AM
Hello,
Ivan kindly gave me this code which is intended to check a range of stock values and add them to a list if they are below 5. It all goes well until i run the macro and it appaers with the error on the Listbox1.clear command. I'm using excel 97, should this affect it at all? ANy body got any idea whats wrong??
Thanks in advance.
Matt
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, "<5")
ReDim vData(vDataCount - 1, 1)
x = -1
For Each cell In Stocklevels
If cell <> "" Then
If cell.Value < 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