Declare Non-contiguous Columns as Arrays

[ward376]

How can I declare multiple columns as arrays where each element of
each array is a combination of adjacent (on the same row) values from
two columns?

I have three columns - the first contains a facility id, the second
contains a material id and the third contains a material id to be
referenced by the material id in the second column.

I want to compare a combination of the first and second columns'
values to a combination of the first and third columns' values to root
out 'loops' (references pointing both ways within each facility) and
instances of the second column values referring to multiple values in
the third column. There will be empties in the third column, but not
the first or second.

Thanks!
Cliff Edwards

 

[Chip Pearson]

I'm not entirely clear what you want to do, but the following code
might get you started. It declares an array Arr with 5 rows and 3
columns and then loads the array with the values from rows 1:5 columns
A, C, and E.


Dim Arr(1 To 5, 1 To 3) As Variant
Dim RI As Long
Dim CI As Long

' load the array with values from rows
' 1 to 5, columns A, C, and E.
For RI = LBound(Arr) To UBound(Arr)
Arr(RI, 1) = Cells(RI, "A")
Arr(RI, 2) = Cells(RI, "C")
Arr(RI, 3) = Cells(RI, "E")
Next RI
' list the array
For RI = LBound(Arr) To UBound(Arr)
Debug.Print Arr(RI, 1), Arr(RI, 2), Arr(RI, 3)
Next RI


Cordially,
Chip Pearson
Microsoft Most Valuable Professional
Excel Product Group, 1998 - 2009
Pearson Software Consulting, LLC
www.cpearson.com
(email on web site)

 

[ward376]

Thanks Chip!

Cliff Edwards