VBA:忽略 For-Each 循环中的条件

Question

问题陈述

我有一些国家和这些国家的州的依赖组合框。我使用 VBA 在第一个组合框中填充唯一值，然后在第二个组合框中动态填充唯一值。该代码似乎忽略了初始传递中的条件。

例如，该代码适用于第一个国家/地区:

但是以下国家/地区错误地保留了第一个州值:

数据

这是数据集，名称为“Country”和“State”。这些名称动态对应于每个标题下方的范围:

名称引用使用以下格式的公式:

=OFFSET(Sheet1!$A$2,0,0,COUNTA(Sheet1!$A:$A),1)

组合框是分别名为“国家”和“州”的 ActiveX 对象。

代码

代码片段:

Private Sub Worksheet_Activate()
'Populate combo box with unique countries.
  Dim arr() As String
  Dim tmp As String
  Dim rng As Range
  Dim ws As Worksheet
  Set ws = Worksheets("Sheet1")
  Me.countries.Clear
  For Each rng In ws.Range("Country")
    If (rng <> "") And (InStr(tmp, rng) = 0) Then
      tmp = tmp & rng & "|"
    End If
  Next rng

  If Len(tmp) > 0 Then tmp = Left(tmp, Len(tmp) - 1)

  arr = Split(tmp, "|")

  Me.countries.List = arr

End Sub

Private Sub countries_lostfocus()
  'Populate dependent combo box with unique states
  'according to selection in countries combo box.
  Dim rng As Range
  Dim ws As Worksheet
  Dim str As String
  Set ws = Worksheets("Sheet1")
  str = countries.Value
  Me.states.Clear
  On Error Resume Next
  For Each rng In ws.Range("State")
    If ((rng.Offset(, -1).Value) = str) And (IsNotInArray(rng.Value, Me.states.List)) Then
      Me.states.AddItem rng.Value
    End If
  Next rng
End Sub

Function IsNotInArray(stringToBeFound As String, arr As Variant) As Boolean
  IsNotInArray = IsError(Application.Match(stringToBeFound, arr, 0))
End Function

其他

新南威尔士州值将存储在所有添加的以下国家/地区的组合框中。

使用 MsgBox 在循环内进行调试，如下所示:

  For Each rng In ws.Range("State")
    If ((rng.Offset(, -1).Value) = str) And (IsNotInArray(rng.Value, Me.states.List)) Then
      MsgBox ("Country: " & str & "; check: " & rng.Offset(, -1).Value)
      Me.states.AddItem rng.Value
    End If
  Next rng

似乎表明，在选择澳大利亚以外的国家/地区时，条件的第一部分无法按预期运行:

Answer 1

尽管我不想看到 NSW 被排除在任何列表之外，但您可以通过在尝试执行 之前测试 arr 变量是否为空来解决您的问题匹配:

Function IsNotInArray(stringToBeFound As String, arr As Variant) As Boolean
    If UBound(Arr) = -1 Then
        IsNotInArray = True
    Else
        IsNotInArray = IsError(Application.Match(stringToBeFound, arr, 0))
    End If
End Function

如果将 arr 作为 ComboBox 的已清除列表传递给该函数，则其 LBound 为 0，UBound 为-1，因此对 UBound 进行测试将防止 Match 崩溃。