vba Excel VBA通过while循环查找值，存储在数组中，并传递给不同的子
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/16898262/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Excel VBA Find Values through while loop, store in array, and pass to different sub
提问by Chris D
First off, I appreciate any help anyone can offer. I am writing a macro that will give the user a form to input a number key. The form will search a spreadsheet for the key and return the corresponding name attached to that key. The data may have multiple names per key and it will vary depending on the key. I want to loop through the data with .Find and .FindNext, and find all the possible names attached to that key (i have accomplished this part). The part I am having trouble with is during the loop, storing each name in an array that I can pass to another sub. I want to pass the array so that the user can click another command button and cycle through the possible names before choosing one.
首先，我感谢任何人可以提供的任何帮助。我正在编写一个宏，它将为用户提供一个输入数字键的表单。该表单将在电子表格中搜索密钥并返回附加到该密钥的相应名称。每个键的数据可能有多个名称，并且会因键而异。我想用 .Find 和 .FindNext 遍历数据，并找到附加到该键的所有可能的名称（我已经完成了这部分）。我遇到问题的部分是在循环期间，将每个名称存储在一个数组中，我可以将其传递给另一个子程序。我想传递数组，以便用户可以单击另一个命令按钮并在选择一个之前循环浏览可能的名称。
Private Sub FindNameButton_Click()
Dim KeyMatch As Long
Dim NameRow As Long
FindName As Range
KeyMatch = KeyBox.Value ' The UserForm input box
With Worksheets("Master List"). Range("D:D")
Set FindName = .Find(What:= KeyMatch, LookAt:= xlWhole, LookIn:= xlValues, MatchCase:= False)
If not FindName Is Nothing Then
FirstAddress = FindName.Address
Do
Application.GoTo FindName
NameRow = ActiveCell.Row
Cells(NameRow, 2).Select 'Selects the name associated with the key identifier
NameBox.Value = ActiveCell.Value 'Fills the UserForm box with the name
' I would like to fill the array here with each name is it passes through but I have no idea how
NameArray(i) = ActiveCell.Value ' ??????
Set FindName = .FindNext(FindName)
Loop While FindName is Nothing and FristAddress <> FindName.Address
End With
End Sub
Private Sub NextNameButton_Click()
Static cnt As Long
If cnt <= Ubound(NameArray) Then
NameBox.Value = NameArray(cnt) 'Fill UserForm Name Box with name from Name Array
Else
cnt = 0
End If
cnt = cnt + 1 ' increase every time button is clicked
End Sub
回答by Dave Lewis
Your question could use additional details about the problem. A few things I noticed.
您的问题可以使用有关该问题的其他详细信息。我注意到的一些事情。
- You are missing an 'End If' for 'If not FindName Is Nothing Then'
- NameArray isn't passed out or into your subroutines. Have you decared NameArray as global?
- NameArray needs to be declared as a dynamic array: Dim NameArray() As Variant.
- You need to use 'Redim Preserve NameArray(newIndxBound)' to increase the size of an array.
- I recommend using 'Option Explicit' to make sure all your variables have been defined.
- You might consider using the function StrCmp for string comparison instead of 'FristAddress <> FindName.Address'.
- 您缺少“如果不是 FindName 则没有”的“如果”
- NameArray 不会传出或进入您的子程序。您是否已将 NameArray 视为全局？
- NameArray 需要声明为动态数组：Dim NameArray() As Variant。
- 您需要使用 'Redim Preserve NameArray(newIndxBound)' 来增加数组的大小。
- 我建议使用“Option Explicit”来确保您的所有变量都已定义。
- 您可能会考虑使用函数 StrCmp 进行字符串比较，而不是使用“FristAddress <> FindName.Address”。
This bit of code that used a global dynamic array might help you out.
这段使用全局动态数组的代码可能会对您有所帮助。
Option Explicit
Public MyArray() As Variant
Sub AddToArray()
Dim indx As Integer
For indx = 0 To 9
ReDim Preserve MyArray(indx)
MyArray(indx) = indx
Next indx
End Sub
Sub RetrieveFromArray()
Dim indx As Integer
Dim sht As Worksheet
Dim rowN As Integer
Set sht = ActiveSheet
rowN = 10
For indx = 0 To 9
sht.Cells(rowN, 3) = MyArray(indx)
rowN = rowN + 1
Next indx
End Sub
