Not ALL values are populating to Combobox for some reason. Why ?

[Corey]

I am trying to set the Selection(row"E") of a Listbox value(by clicking it) to Populate the Combobox
with Values that are in the Same Row("BH")

Private Sub ListBox1_Click()
Application.ScreenUpdating = False
If ComboBox1.ListCount > 0 Then ComboBox1.Clear
Dim LastCell As Long
Dim myrow As Long
Dim nodupes As Collection
On Error Resume Next
LastCell = Worksheets("Data").Cells(Rows.Count, "BH").End(xlUp).Row
With ActiveWorkbook.Worksheets("Data")
..Select
Set nodupes = New Collection
For myrow = 1 To LastCell
If .Cells(myrow, 5).Value = ListBox1.Value Then
If .Cells(myrow, 60) <> "" Then
nodupes.Add .Cells(myrow, 60).Value, CStr(.Cells(myrow, 60).Value)
If Err.Number = 0 Then
ComboBox1.AddItem Cells(myrow, 60) ' I am not getting ALL values listed
End If
End If
End If
Next
End With
Application.ScreenUpdating = True
End Sub

But i ONLY seem to get 1 or 2 values, wher ther are more than that.

Not sure why?

Corey

 

[Alan]

You got all your End If statements in. I'm not quite sure what the problem
is. Your code did show ".select" on a separate line, you might check that.
Other than that, you'll have to wait for one of the gurus to chime in.

Regards,

Alan


Private Sub ListBox1()
Application.ScreenUpdating = False
If ComboBox1.ListCount > 0 Then ComboBox1.Clear
Dim LastCell As Long
Dim myrow As Long
Dim nodupes As Collection
On Error Resume Next
LastCell = Worksheets("Data").Cells(Rows.Count, "BH").End(xlUp).Row
With ActiveWorkbook.Worksheets("Data").Select
Set nodupes = New Collection
For myrow = 1 To LastCell
If .Cells(myrow, 5).Value = Range("H1").Value Then
If .Cells(myrow, 60) <> "" Then
nodupes.Add .Cells(myrow, 60).Value, CStr(.Cells(myrow,
60).Value)
If Err.Number = 0 Then
ComboBox1.AddItem Cells(myrow, 60) ' I am not getting ALL
values listed
End If
End If
End If
Next
End With
Application.ScreenUpdating = True
End Sub

 

[Mike Fogleman]

Check your last statement...
ComboBox1.AddItem Cells(myrow, 60)

You are still in the With...End With block, so should not that line be with
Dot Cells??

ComboBox1.AddItem .Cells(myrow, 60)

Mike F

 

[Guest]

You have to clear the error to check the next value. Adjustments made to
your code.


Private Sub ListBox1_Click()
Application.ScreenUpdating = False
If ComboBox1.ListCount > 0 Then ComboBox1.Clear
Dim LastCell As Long
Dim myrow As Long
Dim nodupes As Collection
On Error Resume Next
LastCell = Worksheets("Data").Cells(Rows.Count, "BH").End(xlUp).Row
With ActiveWorkbook.Worksheets("Data")
.Select
Set nodupes = New Collection
For myrow = 1 To LastCell
If .Cells(myrow, 5).Value = ListBox1.Value Then
If .Cells(myrow, 60) <> "" Then
nodupes.Add .Cells(myrow, 60).Value, CStr(.Cells(myrow, 60).Value)
If Err.Number = 0 Then
ComboBox1.AddItem .Cells(myrow, 60)
else
err.clear
End If
End If
End If
Next
End With
Application.ScreenUpdating = True
End Sub

 

[Corey]

Tom you are a true gentleman.

I am going to analyse my code to yours nd see where i went wrong.
Your code posted works spot on.

Thank You


Corey....
You have to clear the error to check the next value. Adjustments made to
your code.


Private Sub ListBox1_Click()
Application.ScreenUpdating = False
If ComboBox1.ListCount > 0 Then ComboBox1.Clear
Dim LastCell As Long
Dim myrow As Long
Dim nodupes As Collection
On Error Resume Next
LastCell = Worksheets("Data").Cells(Rows.Count, "BH").End(xlUp).Row
With ActiveWorkbook.Worksheets("Data")
.Select
Set nodupes = New Collection
For myrow = 1 To LastCell
If .Cells(myrow, 5).Value = ListBox1.Value Then
If .Cells(myrow, 60) <> "" Then
nodupes.Add .Cells(myrow, 60).Value, CStr(.Cells(myrow, 60).Value)
If Err.Number = 0 Then
ComboBox1.AddItem .Cells(myrow, 60)
else
err.clear
End If
End If
End If
Next
End With
Application.ScreenUpdating = True
End Sub