本文介绍了EXCEL VBA在阵列中存储搜索结果?的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!
问题描述
我找到了一个 .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在阵列中存储搜索结果?的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持!