excel - VBA - 尝试从组合框中删除项目时出现错误 381

Question

我已经尝试了几个小时来处理这个错误，搜索解决方案，但对我来说什么都不清楚。

1. 我已成功将项目从 Excel 导入到组合框（>19 次出现）
2. 现在我在组合中有重复项。我想通过 Excel 表进行迭代，与 Combobox 进行比较并删除不必要的项目（单个项目除外）
3. 我有

错误 381 - 无法获取 Column 属性数组索引。

Dim N As Long, K As Long, counter As Long
With Sheets("Główne")
    N = .Cells(Rows.Count, 12).End(xlUp).Row
End With

Dim ostatnia As Long

ostatnia = Cells(Rows.Count, 11).End(xlUp).Row

For i = 1 To ostatnia
    Range("I" & i + 1).Formula = "=COUNTIFS(L:L,L" & i + 1 & ")"
Next

ComboBox1.Clear
For K = 1 To N
    If Cells(K + 1, 9).Value > 19 Then
        ComboBox1.AddItem Sheets("Główne").Cells(K + 1, 12).Value
    End If
Next K

Range("I2:I" & ostatnia).Select
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
    :=False, Transpose:=False

'############### problem is somewhere below ##############'
For S = 2 To N
    counter = 1
    For iteracjalista = 0 To ComboBox1.ListCount - 1

        If ComboBox1.Column(0, iteracjalista) = Sheets("Główne").Cells(S + 1, 12).Value Then

             If Sheets("Główne").Cells(S + 1, 9).Value > counter Then
                ComboBox1.RemoveItem 1
                counter = counter + 1
             End If

        End If

    Next iteracjalista
Next S

可能问题出在代码的最后一部分。但我不知道我应该如何解决它。你可以帮帮我吗？

标签： excelvbaformscombobox

Answer 1

代替本守则

For K = 1 To N
    If Cells(K + 1, 9).Value > 19 Then
        ComboBox1.AddItem Sheets("Główne").Cells(K + 1, 12).Value
    End If
Next K

使用此代码 - 它在填充 ComboBox 之前消除了重复项

   Dim xList As String, xVal As String

   ' The following populates the ComboBox with Unique Values - No Duplicates
     xList = ""
     ' We are using the colon character ":" as a separator
     ' You may wish to use something else
     For K = 1 To N
        xVal = Cells(K + 1, 9).Value
        If xVal > 19 Then
           If InStr(1, xList, ":" & xVal, vbTextCompare) = 0 Then
              xList = xList & ":" & xVal
           End If
        End If
     Next K
     xList = Mid(xList, 2)  ' Remove the leading : character
     ThisWorkbook.Sheets("Glówne").ComboBox1.List = Split(xList, ":")
   ' Done

然后你可以从ComboBox中取出所有用于删除重复项的现有代码....以下所有内容都可以删除

'############### problem is somewhere below ##############'
For S = 2 To N
    counter = 1
    For iteracjalista = 0 To ComboBox1.ListCount - 1

        If ComboBox1.Column(0, iteracjalista) = Sheets("Główne").Cells(S + 1, 12).Value Then

             If Sheets("Główne").Cells(S + 1, 9).Value > counter Then
                ComboBox1.RemoveItem 1
                counter = counter + 1
             End If

        End If

    Next iteracjalista
Next S