☰

继续在表中搜索字符串，直到找不到更多结果，然后转到下一个搜索词

tag-icon • tag-icon microsoft-excel vba
继续在表中搜索字符串，直到找不到更多结果，然后转到下一个搜索词

我有一个工作代码，它允许我在表中找到一个字符串并返回相邻单元格的值（采用不同的代码）并将其写入数组

Dim raArray As Range
Dim iC As Long

  For iC = 1 To 51 'The array is a 2D Array: arrAllYield(5,50)

     Set raArray = wksA.Cells.Find(What:=arrAllYield(1, iC - 1), LookIn:=xlFormulas, LookAt _
      :=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:= _
      False, SearchFormat:=False)

    If raArray Is Nothing Then
       MsgBox ("Not found")

    Else
      arrAllYield(3, iC - 1) = wksA.Cells(raArray.Row, raArray.Column + 12) 
      arrAllYield(4, iC - 1) = wksA.Cells(raArray.Row, raArray.Column + 26)         
   
    End If

  Next iC

由于该表中有该字符串的多个实例，因此当找到这样的字符串时，我不想转到数组中的下一个搜索词。

搜索词全部位于单个列（“E”）中

如能提供任何提示我将非常感激。

答案1

要在找到匹配项后停止，您可以Exit For按如下方式使用https://learn.microsoft.com/en-us/office/vba/language/reference/user-interface-help/exit-statement

所以你的示例代码看起来像这样

Dim raArray As Range
Dim iC As Long

For iC = 1 To 51 'The array is a 2D Array: arrAllYield(5,50)
    Set raArray = wksA.Cells.Find(What:=arrAllYield(1, iC - 1), LookIn:=xlFormulas, LookAt _
    :=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:= _
    False, SearchFormat:=False)
    If raArray Is Nothing Then
        MsgBox ("Not found")
    Else
        arrAllYield(3, iC - 1) = wksA.Cells(raArray.Row, raArray.Column + 12) 
        arrAllYield(4, iC - 1) = wksA.Cells(raArray.Row, raArray.Column + 26)
        Exit For 'Exit the loop after the first match is found
    End If
Next iC

相关内容