filling and using arrays

[steve_bris]

Hi.

I am having trouble getting data into an array from Access VBA in excel. I have used this code to get the data in.......but it keeps giving an error. I have tried playing with it but it won't work

Dim Role() As Variant
Dim lrow As Long   
Dim i As Long
With Sheets("Sheet1")
[INDENT]lrow = .Range("J65535").End(xlUp).Row
For i = 1 To lrow
[INDENT]Role(i) = .Range("J" & i).Value[/INDENT]
Next
End With[/INDENT]

This won't cycle throught the rows in J, it just stops and gives an error on J1.........the lrow worked fine and finds the lowest value with some text in it...in the example case 7.

To then use this array i have written the following code....I don;'t know if it works yet as I haven't managed to get any data inot the array

For i = 0 To lrow
[INDENT]If Selection = Role(i) Then
' do stuff to it 
End If[/INDENT]Next i

Is this the correct syntax for using an array in the case of comparing if a value is in the array ?

Thanks for any help
Steve

 

[john471]

REDIM (preserve)

I don't know exactly what you're trying to achieve, and if there is a better way to do it, but for a starter, you need to re-dimension your (variable sized) array with boundaries.

If you know how big it needs to be upfront - as in your example you could put it here...

lrow = .Range("J65535").End(xlUp).Row
ReDim Role(1 To lrow)

If you didn't know how big it needed to be until you were finished with it,
you could do something like

        For i = 1 To lrow
            If .Range("J" & i).Value < (somecondition) Then
                nArrayIndex = nArrayIndex + 1
                ReDim Preserve Role(1 To nArrayIndex)
                Role(nArrayIndex) = .Range("J" & i).Value
            End If
        Next

Note the use of the Preserve keyword in this case - means values you have already populated are not lost. If you omit this, then the entire array is redimensioned, and any data it previously held is lost !!

Once you have the array populated with the data to be compared you'll need a loop inside another loop, to assess each cell in the target area against each value of the array.

for the loops you will likely want somthing like

    For i = 1 To lrow
        For nArrayIndex = LBound(Role) To UBound(Role)
            If .Range("B" & i).Value = Role(nArrayIndex) Then  ' note - playing with different column now
                ' do stuff to it
                Exit For ' no point carrying on through the rest of the array
            End If
        Next nArrayIndex
    Next i

HTH

Regards

John

 

[Sergeant]

Steve, I posted an example for you in this thread. John very adeptly explained it again, above.
Just to point out some things:
When you go...
Dim MyArray() as Variant
...basically, nothing happens. You are showing your intentions of using MyArray as an array later.
A typical scenario would be to put this in a module...
Public MyArray() as Variant
...and then further dimension it when you are ready to fill it in a sub.
That is where the ReDim statement comes in. At this point, you need to set some boundaries for it [optional LBound() and required UBound()], or it will never become an array.