Passing range address to array

[rharn]

Hi all,

I am trying to write a procedure that will insert the address of a cell within a range into an array if it meets a requirement. What I have so far is:

                        For R1 = 1 To Max
                            If (Find1.Value < TextBox1) And (Find1.Value > "0") Then
                                Results1(R1) = Find1.Address
                                R1 = R1 + 1
                            End If
                        Next R1

Where Find1.Value is a value within a range and R1 is the counter for the array Results1(). However, this keeps displaying a subscript out of range error. I think this is a very simple error but I am relatively new to VBA so much help would be appreciated!

Thanks!

 

[Ralajer]

The subscript out of range error can mean that the index (R1) is outside the bounds of the array (Results1). Example array has bounds of 1 to 10 and Max = 12. At some point in you code prior to this for loop you should have the two lines of code something like this:

Dim Results1() As String
ReDim Results1(1 to Max)

subscript out of range error can occur for other reasons but I am guess this is were the error occurred hence your posting it.

Below is just your code rewritten to express the string values as numbers as it appears you are making numeric comparison. Not strictly needed but I think it is better practice.

For R1 = 1 To Max
    If (Find1.Value < CDbl(TextBox1.Text)) And (Find1.Value > 0) Then
        Results1(R1) = Find1.Address
        R1 = R1 + 1
    End If
Next R1

 

[Sektor]

Why incrementing R1 if it's incremented automatically?
This style of coding is error-prone.

 

[rharn]

Hi Rob,

Thanks for the tip. Since I am creating a dynamic array, is there a way to size the array as the loop progresses? Currently my Results1() array is sized somewhat arbitrarily to the same size as the data range to ensure that it can fit all data. But I feel it might be more efficient if I can size the size of the results array so it does not have any blank rows in the array.

Thanks in advance!

 

[Sektor]

[COLOR="Blue"]Sub[/COLOR] Arrs()
    
    [COLOR="Blue"]Dim[/COLOR] Max [COLOR="Blue"]As[/COLOR] [COLOR="Blue"]Long[/COLOR], R1 [COLOR="Blue"]As[/COLOR] [COLOR="Blue"]Long[/COLOR], i [COLOR="Blue"]As[/COLOR] [COLOR="Blue"]Long[/COLOR], Results1() [COLOR="Blue"]As[/COLOR] [COLOR="Blue"]Variant[/COLOR]
    
    Max = 100
    
    [COLOR="Blue"]For[/COLOR] R1 = 1 [COLOR="Blue"]To[/COLOR] Max
        [COLOR="Blue"]If[/COLOR] (Find1.Value < TextBox1) [COLOR="Blue"]And[/COLOR] (Find1.Value > "0") [COLOR="Blue"]Then[/COLOR]
            i = i + 1
            [COLOR="Blue"]ReDim[/COLOR] [COLOR="Blue"]Preserve[/COLOR] Results1(i)
            Results1(i) = Find1.Address
        [COLOR="Blue"]End[/COLOR] [COLOR="Blue"]If[/COLOR]
    [COLOR="Blue"]Next[/COLOR] R1
    
[COLOR="Blue"]End[/COLOR] [COLOR="Blue"]Sub[/COLOR]