Error 13 with combobox

[Linebaker]

Hi everyone,

I had a error when I run my code.

Private Sub ComboBox3_Change()
Dim L As Long
Dim tabtemp as variant

With Worksheets("Sheet1")
L = .Range("a5000").End(xlUp).Row
tabtemp = .Range("A2:K" & L)
End With

For L = 1 To UBound(tabtemp, 1) ------------------------------> error 13 ?
If tabtemp(L, 1) = CLng(ComboBox3.Value) Then
ListBox1.AddItem tabtemp(L, 1)
ListBox2.AddItem tabtemp(L, 2)
ListBox3.AddItem tabtemp(L, 3)
ListBox4.AddItem tabtemp(L, 4)
ListBox5.AddItem tabtemp(L, 8)
ListBox6.AddItem tabtemp(L, 6)
ListBox7.AddItem tabtemp(L, 7)
ListBox8.AddItem tabtemp(L, 5)
End If
Next L

End Sub

Anyone can help ? Please

Thanks

 

[Jim Thomlinson]

tabtemp is not an array it is a range object (A Group of 1 or more cells) try
something more like this...

dim rngToSearch as range
dim rngCurrent as range

set rngToSearch = range(sheets("Sheet1").Range("A2"), _
sheets("Sheet1").Range("A65536").End(xlUP))

for each rngCurrent in rngToSearch
If rngCurrent.Value = CLng(ComboBox3.Value) Then
ListBox1.AddItem rngCurrent.Value
ListBox2.AddItem rngCurrent.Offset(0,1).Value
ListBox3.AddItem rngCurrent.Offset(0,2).Value
ListBox4.AddItem rngCurrent.Offset(0,3).Value
ListBox5.AddItem rngCurrent.Offset(0,7).Value
ListBox6.AddItem rngCurrent.Offset(0,5).Value
ListBox7.AddItem rngCurrent.Offset(0,6).Value
ListBox8.AddItem rngCurrent.Offset(0,4).Value
End If

Next rngCurrent

 

[Linebaker]

Corrected.