vba 在 for 循环期间删除项目

Remove item during for loop

提问by Mateen Ulhaq

What does tlistcontain after I do:

什么是tlist包含我做后：

Dim tlist As Collection
tlist.Add ("AA")
tlist.Add ("BB")
tlist.Add ("CC")
tlist.Add ("DD")

For i = 1 To tlist.Count
    If i = 2 Then tList.Remove (2)
Next

I am hoping it will be:

我希望它将是：

Index | Value
-------------
1     |  "AA"
3     |  "BB"
4     |  "CC"

But I fear it will be:

但我担心它会是：

Index | Value
-------------
1     |  "AA"

---

---

---

---

This is what my code looks like so far:

这是我的代码到目前为止的样子：

Dim tlist As Collection
tlist.Add ("KA")
tlist.Add ("KIC")
tlist.Add ("KS")
tlist.Add ("NC")

' Loop through each cell in row.
For Each mycells In myrow.Columns
    Dim i As Integer

    ' Loop through each regex in list.
    For i = 1 To tlist.Count
        matches = Regex(tlist(i))

        ' If match found in cell, copy row, and remove element (to prevent duplicate rows).
        If matches >= 1 Then
            Call CopyRow(myrow, _
                         ActiveSheet.Index, _
                         ActiveSheet.Index + i)

            tlist.Remove (i)
        End If
    Next
Next

Is Collectioneven the right data type to use?

是Collection即使使用正确的数据类型？

回答by Reafidy

To answer your first question the item removed from the collection will be "BB". The other items will remain. You are simply removing the second item in the list: .Remove(2)

要回答您的第一个问题，从集合中删除的项目将是“BB”。其他项目将保留。您只需删除列表中的第二项： .Remove(2)

To answer your second question: IT depends on what you are doing. I nearly always prefer to work with the dictionary object. It has more functionality and you can check if an element exists and assign an item to each key.

回答您的第二个问题：IT 取决于您在做什么。我几乎总是喜欢使用字典对象。它具有更多功能，您可以检查元素是否存在并为每个键分配一个项目。

'// Using With but you could use Set dic = CreateObject("Scripting.Dictionary")
With CreateObject("Scripting.Dictionary")

    .Add "AA", Nothing
    .Add "BB", Nothing
    .Add "CC", Nothing
    .Add "DD", Nothing

    If .exists("BB") Then .Remove ("BB")

    For Each Key In .keys
      Debug.Print Key
    Next Key

End With

Above code prints AA, CC, DD

上面的代码打印 AA、CC、DD

回答by chris neilsen

I aggree with Reafidy, a Dictionarywould work better.

我同意 Refidy，aDictionary会更好。

There a are also a number of other issues in your code. Here's a refactored version. Note that I have used early binding, so set a reference to Microsoft Scripting Runtimeand Microsoft VBScript Regular expressions 5.5

您的代码中还有许多其他问题。这是一个重构版本。请注意，我使用了早期绑定，因此设置了对Microsoft Scripting Runtime和的引用Microsoft VBScript Regular expressions 5.5

Sub Muntoo()
    Dim re As RegExp
    Dim myCells As Range
    Dim myRow As Range
    Dim Key As Variant
    Dim tlist As Dictionary

    On Error GoTo EH

    Set tlist = New Dictionary
    Set re = New RegExp

    tlist.Add 1, "KA"
    tlist.Add 2, "KIC"
    tlist.Add 3, "KS"
    tlist.Add 4, "NC"

    ' Set row reference 
    Set myRow = Intersect(ActiveSheet.UsedRange, ActiveCell.EntireRow)  

    re.Global = True ' or false
    re.IgnoreCase = True ' or false

    ' Loop through each cell in row.
    For Each myCells In myRow.Cells
        ' Loop through each regex in list.
        If myCells.Value <> "" Then
            For Each Key In tlist.Keys
                re.Pattern = tlist.Item(Key)
                ' If match found in cell, copy row, and remove element (to prevent duplicate rows).
                If re.Test(myCells.Value) Then
                    Call CopyRow(myRow, _
                                 ActiveSheet.Index, _
                                 ActiveSheet.Index + Key)

                    tlist.Remove Key
                End If
            Next
        End If
    Next
CleanUp:
    On Error Resume Next
    Set re = Nothing
    tlist.RemoveAll
    Set tlist = Nothing
Exit Sub
EH:
    Resume
    GoTo CleanUp
End Sub

Private Sub CopyRow(rng As Range, i1 As Long, i2 As Long)

End Sub

回答by GregM

Not sure exactly, but if you want to remove an element at a specified index, you should look at the accepted answer of this question:

不确定，但如果要删除指定索引处的元素，则应查看此问题的已接受答案：

Deleting Elements in an Array if Element is a Certain value VBA

如果元素是某个值 VBA，则删除数组中的元素

Seems to do what you want :)

似乎做你想做的:)