Copying array into disjoint range.

[Lance Roberts]

Seems like I alway have trouble when dealing with disjoint ranges.

This time I'm trying to do a simple copy of a matrix (2-D array) into a
disjoint range.
The dimensions of the disjoint range and the array are the same, but I get
gobbledygook (without pattern) when I do the assignment.
The first line actually copies fine, but then the other lines that are
disjoint from the first, won't work.

Dim matrix As Variant
'code to fill matrix
Range("testrange") = matrix

 

[Rick Rothstein \(MVP - VB\)]

I am not 100% sure what you mean by "disjoint range" or "the dimensions of
the disjoint range and the array are the same", but my guess is you have a
normal contiguous range that you refer to as "matrix" and a "disjoint" range
with the same number of rows as the matrix has, but which are not
necessarily adjacent to each other, but each of its rows has the same number
of columns and that number is the same as the number of columns in the
matrix. If that is the case, give this code a try...

Sub CopyMatrixToDisjointRows()
Dim R As Range
Dim MatrixRange As Range
Dim CopyToRange As Range
Dim RowIndex As Long
Set MatrixRange = Range("C3:F7")
Set CopyToRange = Range("J8:M8,M1010,B15:E15,D20:G20")
RowIndex = 1
For Each R In CopyToRange.Rows
MatrixRange.Rows(RowIndex).Copy R
RowIndex = RowIndex + 1
Next
End Sub

Rick

 

[Lance Roberts]

Rick,

You've got most of it.
The big difference is that my matrix is a Variant Array object (filled by a
Recordset Getrows method).
I don't know of a Rows method for the array, but the documentation is very
sparse on multidimensional arrays in VBA, so if you know of any way to
extract rows or columns, that'd be great.
I've been searching for literal days on the web to uncover a helpful syntax.

--
Lance Roberts
Control Systems Engineer
Golden Valley Electric Assoc.

I am not 100% sure what you mean by "disjoint range" or "the dimensions of
the disjoint range and the array are the same", but my guess is you have a
normal contiguous range that you refer to as "matrix" and a "disjoint" range
with the same number of rows as the matrix has, but which are not
necessarily adjacent to each other, but each of its rows has the same number
of columns and that number is the same as the number of columns in the
matrix. If that is the case, give this code a try...

Sub CopyMatrixToDisjointRows()
Dim R As Range
Dim MatrixRange As Range
Dim CopyToRange As Range
Dim RowIndex As Long
Set MatrixRange = Range("C3:F7")
Set CopyToRange = Range("J8:M8,M1010,B15:E15,D20:G20")
RowIndex = 1
For Each R In CopyToRange.Rows
MatrixRange.Rows(RowIndex).Copy R
RowIndex = RowIndex + 1
Next
End Sub

Rick

 

[Alan Beban]

Rick,

You've got most of it.
The big difference is that my matrix is a Variant Array object (filled by a
Recordset Getrows method).
I don't know of a Rows method for the array, but the documentation is very
sparse on multidimensional arrays in VBA, so if you know of any way to
extract rows or columns, that'd be great.
I've been searching for literal days on the web to uncover a helpful syntax.

Application.Index(myArray,n,0), where myArray is a 2-D array, will
return the nth row.

Alan Beban

 

[Rick Rothstein \(MVP - VB\)]

I have virtually no experience with databases, so I'll be fumbling around
here a little bit. When you say "Variant Array object (filled by a Recordset
Getrows method)", are you talking about an ADO or DAO record set object? If
so, the Range object has a CopyFromRecordset Method which might prove
useful. If you have such a record set object, let's call it RS, then I think
you might be able to modify my original code like this (remember, I am
guessing here)...

Sub CopyMatrixToDisjointRows()
Dim R As Range
Dim MatrixRange As Range
Dim CopyToRange As Range
Dim RowIndex As Long
Dim RS As Recordset
'
' Obtain your RS Recordset here
'
MatrixRange.CopyFromRecordset RS
Set CopyToRange = Range("J8:M8,M1010,B15:E15,D20:G20")
RowIndex = 1
For Each R In CopyToRange.Rows
MatrixRange.Rows(RowIndex).Copy R
RowIndex = RowIndex + 1
Next
End Sub

Obviously, if this works, you will need to adjust the Range you are setting
the CopyToRange range to to match your actual addresses.

Rick

Rick,

You've got most of it.
The big difference is that my matrix is a Variant Array object (filled by
a
Recordset Getrows method).
I don't know of a Rows method for the array, but the documentation is very
sparse on multidimensional arrays in VBA, so if you know of any way to
extract rows or columns, that'd be great.
I've been searching for literal days on the web to uncover a helpful
syntax.

 

[Lance Roberts]

Whoooooppppppiiiiiieeeee!

I've looked through hundreds of pages in help files, books and the web, and
that was EXACTLY what I needed. I only had to move the array to a 1-based
array and it worked great. Thanks for all the help.

 

[Lance Roberts]

Rick,

If I could copy directly into the range, that would work great,
but I have to manipulate the data first.

Also, in regards to my last reply, you only have to massage the row index in
the Index function, i.e. array row 0 will be accessed with a value 1, the
function doesn't use the array index values but absolute addressing on the
array.

My final code:

For i = 0 To 10
Range("TSR_" & i) =
Application.WorksheetFunction.Index(prelimmatrix, i + 1, 0)
Next i