在For循环中搜索列表对象表的列 [英] Searching through List Object Table's Column in For Loop

问题描述

因此，用最简单的解释；我想搜索我的 ListObjecttable 数据(特别是第9列)，如果值符合条件，则将值复制到另一个工作表中(暂时忽略此位).

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).

当前，我的代码将表设置为列表对象，但由于我将使用多个列，因此未指定列.当我遍历第9列时，尽管它为我提供了运行时错误9.我是否错误地引用了该列?

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

该行是否如果FullAssLi.ListColumns(9).Value = UserForm2.SourceLiBo.Value然后不正确?

用户窗体正在通过在之前运行的代码中设置的值.

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

推荐答案

您一直在引用整列:

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

这篇关于在For循环中搜索列表对象表的列的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！