开发者

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.

0

上一篇:

下一篇:

精彩评论

暂无评论...
验证码 换一张
取 消

最新问答

问答排行榜