EXCEL VBA在阵列中存储搜索结果？ [英] EXCEL VBA Store search results in an array?

问题描述

我找到了一个 .Find 函数来搜索一个列为我想要的值，是否可以保存数组中的所有地址？

代码如下所示：

 设置wsRaw =工作表（raw_list）
设置oRange = wsRaw.Columns（PhaseCol）
 
 SearchString =控件
 
设置aCell = oRange.Find（什么：= SearchString，LookIn：= xlValues，_ 
 LookAt：= xlWhole，SearchOrder = = xlByRows，SearchDirection：= xlNext，_ 
 MatchCase：= False，SearchFormat：= False）
 
如果不是aCell没有，然后
设置bCell = aCell 
 FoundAt = aCell.Address 
 Do While ExitLoop = False 
设置aCell = oRange.FindNext（After：= aCell）
 
如果不是aCell是没有，然后
如果aCell.Address = bCell.Address然后退出Do 
 FoundAt = FoundAt& ，& aCell.Address 
 Else 
 ExitLoop = True 
 End If 
 Loop 
 Else 
 MsgBox SearchString& not Found
 End If 
 
 MsgBox搜索字符串已找到这些位置：& FoundAt 
退出Sub 
  

至于现在我有一个 MsgBox 只是为了显示结果。这个想法是将结果存储在数组中，如果可能的话。

解决方案

是的，你可以这样做。看这个例子

  Dim MyResults（）As String 
 Dim n As Long 
 
n = 1 
 
'
'~~>其余的代码
'
 
如果不是aCell是没有
设置bCell = aCell 
 
 ReDim保存MyResults（n）
 MyResults （n）= aCell.Address 
n = n + 1 
 
 Do While ExitLoop = False 
设置aCell = oRange.FindNext（After：= aCell）
 
如果不是aCell没有，然后
如果aCell.Address = bCell.Address然后退出Do 
 ReDim保留MyResults（n）
 MyResults（n）= aCell.Address 
n = n + 1 
 Else 
 ExitLoop = True 
如果
循环
 Else 
 MsgBox SearchString& 找不到
如果
  

然后，您可以稍后循环显示数组结果

  For i = LBound（MyResults）To UBound（MyResults）
 Debug.Print MyResults（i）
 Next i 
  

As the title say is it possible and how?

I have found a .Find function to search a column for the values I want, is it then possible to save all the addresses in an array?

The code looks like this:

Set wsRaw = Worksheets("raw_list")
Set oRange = wsRaw.Columns(PhaseCol)

SearchString = "control"

Set aCell = oRange.Find(What:=SearchString, LookIn:=xlValues, _
            LookAt:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
            MatchCase:=False, SearchFormat:=False)

If Not aCell Is Nothing Then
    Set bCell = aCell
    FoundAt = aCell.Address
    Do While ExitLoop = False
        Set aCell = oRange.FindNext(After:=aCell)

        If Not aCell Is Nothing Then
            If aCell.Address = bCell.Address Then Exit Do
            FoundAt = FoundAt & ", " & aCell.Address
        Else
            ExitLoop = True
        End If
    Loop
Else
    MsgBox SearchString & " not Found"
End If

MsgBox "The Search String has been found these locations: " & FoundAt
Exit Sub

As for now I have a MsgBox just to show the results. The idea was to store the result in an array if possible.

解决方案

Yes you can do that. See this example

Dim MyResults() As String
Dim n As Long

n = 1

'
'~~> rest of the code
'

If Not aCell Is Nothing Then
    Set bCell = aCell

    ReDim Preserve MyResults(n)
    MyResults(n) = aCell.Address
    n = n + 1

    Do While ExitLoop = False
        Set aCell = oRange.FindNext(After:=aCell)

        If Not aCell Is Nothing Then
            If aCell.Address = bCell.Address Then Exit Do
            ReDim Preserve MyResults(n)
            MyResults(n) = aCell.Address
            n = n + 1
        Else
            ExitLoop = True
        End If
    Loop
Else
    MsgBox SearchString & " not Found"
End If

You can then later loop through the array to show the results

For i = LBound(MyResults) To UBound(MyResults)
    Debug.Print MyResults(i)
Next i

这篇关于EXCEL VBA在阵列中存储搜索结果？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！