VBA:: Passing range results into an array
I am currently working on a search function in VBA that will take the results from a searched range and input the address of the cell location into an array.
I have attempted to set up the array with the following code.
Dim FindRange1 as Range
Dim Find1 as Range
Dim Results1() as Variant
Dim R1 as integer
Dim Max as integer
Max = Range("E7:E1000").Cells.Count
Set FindRange1 = Worksheets("Properties").Range("P7:P1000")
If ILsearch.P1B1.Value = True Then
开发者_如何转开发 For R1 = 1 To Max
For Each Find1 In FindRange1
If (Find1.Value < TextBox1) And (Find1.Value > "0") Then
Results1(R1) = Find1.Address
End If
Next Find1
Next R1
End If
You need to dimension the array;
redim Results1(Max) '//this will leave an empty Results1(0)
Better to use a 0 index;
redim Results1(Max-1)
...
Results1(R1 - 1) = Find1.Address
Note that this is creating an array with "gaps" where only indexes that meet your criteria are filled.
精彩评论