CODEWITHSUNDEEP
excelvba
Breakingfaith
Breakingfaith

Reputation: 63

Searching through List Object Table's Column in For Loop

So in the simplest explanation; I want to search through my ListObjecttable of data (specifically column 9) and copy values to another sheet if they match a criteria (ignore this bit for now).

Currently my code sets out the table as a list object but doesn't specify the column due to the fact I'll be using multiple columns. When I go to loop through the ninth column though it provides me a runtime error 9. Am I referring to the column incorrectly?

Sub RequestedAssetList()

Dim FullAssLi As ListObject, RowToPasteTo As Long 'Defining the Table and Range
Set FullAssLi = ThisWorkbook.Sheets("Asset List").ListObjects("AssListTab") 'Set FullAsset Lists as the Asset Table

With ThisWorkbook 'Within the workbook
    .Sheets.Add(After:=.Sheets(.Sheets.Count)).Name = "Assets" 'Adds a sheet at the end of the workbook called Assets
End With

With ThisWorkbook.Sheets("Assets")
    RowToPasteTo = .Cells(.Rows.Count, "A").End(xlUp).Row + 1 'Sets variable as the next exmpy row on column A of Assets' tab

    For i = 1 To ThisWorkbook.Sheets("Asset List").ListObjects("AssListTab").Range.Rows.Count 'For first interger to last row in table
        If FullAssLi.ListColumns(9).Value = UserForm2.SourceLiBo.Value Then

        End If
    Next i
End With
End Sub

Is the line If FullAssLi.ListColumns(9).Value = UserForm2.SourceLiBo.Value Then incorrect?

EDIT: The userform is bringing through a value set in the code run before.

Upvotes: 0

Views: 107

Answers (1)

GuidoT
GuidoT

Reputation: 312

You are refering to the full column all the time:

If FullAssLi.ListColumns(9).Value = UserForm2.SourceLiBo.Value Then

Instead try something like:

Dim LRow as ListRow
For Each LRow In FullAssLi.ListRows 
    If LRow.Range.Cells(9).Value = UserForm2.SourceLiBo.Value Then
    End If
Next

Upvotes: 2

Related Questions