populate a combobox based on other combobox using table ranges

Question

I have the following code to populate two comboboxes based on one other combobox. before it worked perfectly with normal ranges. Now as I modified it to work with tables it does not work anymore. As the code says, the destination comboboxes are column 2 of two tables and column one of the two tables have similar values which is actually the reference combobox.

Private Sub SPL2loadcombos()

Dim sh As Worksheet
Dim Ir As Long
Dim Ir2 As Long
Dim nameColumn As Range
Dim namecolumn2 As Range
Dim gradescolumnweld As Range
Dim gradesColumn As Range
Dim selectedName As String
Dim i As Long
Dim j As Long
Dim value As String
Dim combo_spl2load As ComboBox


Set sh = ThisWorkbook.Sheets("pl2_steel")


Ir = sh.Cells(Rows.Count, 1).End(xlUp).End(xlUp).Row
Ir2 = sh.Cells(Rows.Count, 7).End(xlUp).End(xlUp).Row


Set nameColumn = sh.ListObjects("tbl_spl2elements").ListColumns(1).DataBodyRange
Set namecolumn2 = sh.ListObjects("tbl_spl2weldings").ListColumns(1).DataBodyRange


Set gradesColumn = sh.ListObjects("tbl_spl2elements").ListColumns(2).DataBodyRange
Set gradescolumnweld = sh.ListObjects("tbl_spl2weldings").ListColumns(2).DataBodyRange


selectedName = Me.combo_spl2load.value


Combo_spl2elementload.Clear
Combo_spl2weldload.Clear


For i = 8 To Ir
    
    If nameColumn.Cells(i).value = selectedName Then
        
        Me.Combo_spl2elementload.AddItem gradesColumn.Cells(i).value
    End If
Next i

   
For i = 8 To Ir2
    
    If namecolumn2.Cells(j).value = selectedName Then
        
        Me.Combo_spl2weldload.AddItem gradescolumnweld.Cells(j).value
    End If
Next i

End Sub

As my problem is still not solved even with the corrections from the comments, I am uploading here a screenshot from my worksheet:

The counter of your second loop is `i` but you're using `.Cells(j)` — Super Symmetry, Jul 25 '23 at 09:25
I fixed it, doesnt still work. I had changed the sedond counter to (j) as a possible solution. But it is not working anyways — shahin syr, Jul 25 '23 at 09:44
It is difficult to understand why you're looping from `8 to Ir2` without looking at your data. Are you able to add a screenshot of your sheet? It makes more sense to loop `For i = 1 To nameColumn.Cells.Count` and `For j = 1 To namecolumn2.Cells.Count` — Super Symmetry, Jul 25 '23 at 09:59
Also you need to be more specific: What exactly "*does not work*". e.g. are the comboboxes completely empty? Are they partially populated? Are you getting errors? — Super Symmetry, Jul 25 '23 at 10:02
You are totally right. To be more specific. I start loop from i=8 because the two tablse both start from the 8th row (7th row is header). I dont get any error but both destination combos are empty . The rest is quite clear I guess. If not please let me know and I edit the question post with a screenshot. — shahin syr, Jul 25 '23 at 10:10
`nameColumn` is *just the data from the listobject* - it doesn't extend above the listobject, so you don't need to account for where the list is on the worksheet. As already noted - start looping from 1. — Tim Williams, Jul 25 '23 at 15:15

score 0 · Answer 1 · answered Jul 27 '23 at 07:07

Please allow me to inform that finally I edited the following code which is working perfectly for my need:

Private Sub SPL2loadcombos()

Dim sh As Worksheet
Dim Ir As Long
Dim Ir2 As Long
Dim selectedName As String
Dim i As Long
Dim j As Long
Dim tblelements As ListObject
Dim tblweldings As ListObject

Set tblelements = ThisWorkbook.Sheets("pl2_steel").ListObjects("Tbl_spl2elements")
Set tblweldings = ThisWorkbook.Sheets("pl2_steel").ListObjects("Tbl_spl2weldings")

Set sh = ThisWorkbook.Sheets("pl2_steel")


Ir = sh.Cells(Rows.Count, 1).End(xlUp).End(xlUp).Row
Ir2 = sh.Cells(Rows.Count, 7).End(xlUp).End(xlUp).Row


selectedName = Me.combo_spl2load.value


Combo_spl2elementload.Clear
Combo_spl2weldload.Clear


For i = 1 To Ir

If tblelements.DataBodyRange(i, 1).value = selectedName Then
    
    Me.Combo_spl2elementload.AddItem tblelements.DataBodyRange(i, 2).value
End If
Next i


For j = 1 To Ir2

If tblweldings.DataBodyRange(j, 1).value = selectedName Then
    
    Me.Combo_spl2weldload.AddItem tblweldings.DataBodyRange(j, 2).value
End If
Next j

End Sub

Your answer could be improved (and so future readers could learn from your answer) by adding an explanation of the problem you found with your original code and how you resolved it with this new code. — JohnM, Jul 28 '23 at 05:59

populate a combobox based on other combobox using table ranges

1 Answers1