HOW TO ASSIGN 2 DIMENSION ARRAY VALUES FROM 2 COLUMNS?

[Guest]

I need to assign dimension 1 with col A data and dimension 2 with col B data.
IOW, element 1 would consist of A1, B1, element 2 A2, B2... The number of
rows is not always the same. Can somebody provide an example of code to
accomplish this? Thanks! -JEFF-

 

[Tom Ogilvy]

You can assign it to a variant variable
Dim v as Variant
in xl2000 and later you can also assign it to a dynamic variant array
Dim v() as Variant
However, assigning it to a variant works as well and is compatible back to
excel 5 where vba was introduced.

Dim v as Variant
v = Range("A1").currentRegion.Resize(,2).Value

for i = 1 to ubound(v,1)
for j = 1 to ubound(v,2)
debug.print i, j, v(i,j)
Next
Next

 

[Doug Glancy]

Jeff,

I believe this does it:

Sub test()
Dim test_array()
Dim first_dimension_count As Long
Dim i As Long

first_dimension_count = Range("A" & Rows.Count).End(xlUp).Row
ReDim test_array(1 To first_dimension_count, 1 To 2)
test_array = Range("A1:B" & first_dimension_count)
End Sub

hth,

Doug

 

[Guest]

Thank You, Doug, works great!
-JEFF-

Jeff,

I believe this does it:

Sub test()
Dim test_array()
Dim first_dimension_count As Long
Dim i As Long

first_dimension_count = Range("A" & Rows.Count).End(xlUp).Row
ReDim test_array(1 To first_dimension_count, 1 To 2)
test_array = Range("A1:B" & first_dimension_count)
End Sub

hth,

Doug