Update For loop from Cells references to dynamic Excel table references

[Ben M]

Hi,

Is there a way to update this loop so that the Cells (i,7) and Cells (i,4) refer to each cell in the range of a table column (perhaps using the column header to choose the row)?

[
Sub CreateNewListEng()
    
    Application.ScreenUpdating = False
    
    Dim Theme                As String
    Dim i, FinalRow          As Integer
    Dim TestPage, Data       As Worksheet
    Dim TblData, TblTopic    As ListObject
    
    Set Test = Sheet1
    Set Data = Sheet2
    Set TblData = Worksheets("Data").ListObjects("T_VocabList")
    Set TblTopic = Worksheets("Test").ListObjects("T_Topic")
       
    Theme = Worksheets("Test").ListObjects("T_Topic").DataBodyRange(1, 1)
    FinalRow = TblData.Range.Rows.Count
    
    Sheet1.Range("C4:E1000").ClearContents
    
    For i = 1 To FinalRow
    
    Data.Select
        
        If Cells(i, 7) = Theme Then
            Cells(i, 4).Copy
            Test.Select
            Range("C1000").End(xlUp).Offset(1, 0).PasteSpecial xlPasteFormulasAndNumberFormats
            Data.Select
            Cells(i, 6).Copy
            Test.Select
            Range("D1000").End(xlUp).Offset(1, 0).PasteSpecial xlPasteFormulasAndNumberFormats
            Data.Select
        End If
        
    Next i
    
    Test.Select
    Range("E4").Select
    
End Sub
/CODE]

I think it can be done with something like this perhaps?

[I]iListRow In ActiveSheet.ListObjects("Tbl...........").ListRows[/I]

Thanks!

 

[jasonb75]

Instead of a numbered loop, you could loop through a range.

For Each c In Range("TableName[ColumnHeader]")

It is possible that you don't even need to loop, what exactly is it that you're trying to do?

 

[Ben M]

I choose a topic from a drop down list and then press a button to select words from a vocab list that relate to that topic. The words are copied from a second sheet and pasted into a list on the first sheet. Does that make sense?

 

[jasonb75]

It would probably make more sense looking at it than reading a description.

Is it simply a case of copying all of the cells that are still visible (in the table column) after the filter is applied?