VBA EXCEL-将列表框项与单元格值（字符串）进行比较

VBA EXCEL- Comparing listbox item to cell value (string)

提问by drD

My list box is on a form. When a user selected list items they are stored in a column. Later when m edit button is clicked I would like to read in these values and add them onto a selected list (Listbox_selectedCategories) on the form and remove them from the other list box (Listbox_categories) The error that the VBA editor is throwing is error 381 could not get list property. Invalid property array index. I have checked both the value of x and index in the immediate window which are iterating as expected.I have also tried this without the Cstr function,same error. The compiler is highlighting the if statement as the point of error. Im sure this is some fundamental misunderstanding I would appreciate any help or direction. Thanks.

我的列表框在表单上。当用户选择列表项时，它们存储在列中。稍后当单击 m 编辑按钮时，我想读入这些值并将它们添加到表单上的选定列表 (Listbox_selectedCategories) 中，然后将它们从另一个列表框 (Listbox_categories) 中删除 VBA 编辑器抛出的错误是错误 381无法获取列表属性。无效的属性数组索引。我已经检查了立即窗口中 x 和 index 的值，它们按预期进行迭代。我也试过没有 Cstr 函数，同样的错误。编译器将 if 语句突出显示为错误点。我确定这是一些根本性的误解，我将不胜感激任何帮助或指导。谢谢。

 Public Sub dataLoad()
 Dim x As Integer
 x = 0

NewQueryForm.targetingDescription.value = ActiveCell.Offset(1, 0).value
NewQueryForm.booleanDescription.value = ActiveCell.Offset(1, 1).value
NewQueryForm.startDate.value = ActiveCell.Offset(1, 3).value
NewQueryForm.endDate.value = ActiveCell.Offset(1, 4).value
NewQueryForm.dfpCount.value = ActiveCell.Offset(1, 5).value

NewQueryForm.Text_300Rates = ActiveCell.Offset(1, 8).value

NewQueryForm.Text_160Rates = ActiveCell.Offset(2, 8).value

NewQueryForm.Text_728Rates = ActiveCell.Offset(3, 8).value

NewQueryForm.Text_PollRates = ActiveCell.Offset(4, 8).value

NewQueryForm.Text_CMRates = ActiveCell.Offset(5, 8).value

NewQueryForm.Text_ExCMRates = ActiveCell.Offset(6, 8).value



Call NewQueryForm_Initialize

Sheets("CAT").Activate
Range("A1").Activate
ActiveCell.Offset(0, loopCount).Activate

While ActiveCell.Offset(1, 0).value <> ""
x = x + 1
Dim index As Integer

'Adding and removing items from list
NewQueryForm.ListBox_selectedCategories.AddItem ActiveCell.Offset(x - 1, 0).value

For index = 0 To NewQueryForm.ListBox_categories.ListCount - 1

If CStr(NewQueryForm.ListBox_categories.List(index)) = ActiveCell.Offset(x - 1, 0).value Then
            NewQueryForm.ListBox_categories.RemoveItem (index)
        End If

Next index

'Adding and Subtracting from global percent label variables
selectedCategoryLabel = selectedCategoryLabel + categoryPercent(ActiveCell.Offset(x - 1, 0).value)
categoryLabel = categoryLabel - categoryPercent(ActiveCell.Offset(x - 1, 0).value)

'Setting next cell down active
ActiveCell.Offset(1, 0).Activate
Wend

'updating labels
 NewQueryForm.selectedPercent.Caption = CStr(Round(selectedCategoryLabel, 2)) & "%"
 NewQueryForm.categoryPercent = CStr(Round(categoryLabel, 2)) & "%"





NewQueryForm.Show
End Sub

回答by bonCodigo

Following is the code I tried. I got the exact same error. And here is how I solved it.

以下是我试过的代码。我得到了完全相同的错误。这就是我解决它的方法。

BINGO: EXIT THE LOOP :)

宾果游戏：退出循环 :)

Option Explicit    
'--show form on Sheet with Reg Edit mode
 Private Sub CommandButton1_Click()
    UserForm1.Show vbModeless
 End Sub   

'--Form codes within Form

Option Explicit

Private Sub cmdAddRange_Click()
Dim ws As Worksheet
Dim rng As range
Dim inputArray As Variant
Dim lRow As Long
Dim index As Integer

Set ws = Sheets(1)
Set rng = ws.range("B2")
lRow = ws.range("B" & ws.Rows.Count).End(xlUp).Row
inputArray = Application.WorksheetFunction.Transpose(rng.Resize(lRow).Value)

'Adding item to List one by one from Array
'For index = LBound(inputArray) To UBound(inputArray)
'UserForm1.ListBox1.AddItem inputArray(i)
'Next i

'Adding items to List box at ones from Array
UserForm1.ListBox1.List = inputArray
UserForm1.ListBox2.List = inputArray

End Sub

Private Sub cmdRemoveRange_Click()
Dim ws As Worksheet
Dim index As Integer

    Set ws = Sheets(1)

    '-- Remove  from listbox1
    For index = 0 To UserForm1.ListBox1.ListCount - 1

        If Not IsEmpty(ActiveCell.Value) Then
            If UserForm1.ListBox1.List(index) = ActiveCell.Value Then
                UserForm1.ListBox1.RemoveItem (index)
                MsgBox ActiveCell.Value & " deleted. And Golden Line *Exit For*"
                Exit For '-- golden line
            End If
        End If
    Next index

End Sub

Here is the Form I tried out:

这是我试过的表格：