One-D Array to Column Problem

  • Thread starter Thread starter John
  • Start date Start date
J

John

Greetings All,

The following code is *supposed* to take data returned from a database
query, extract one field of data to a 1-D array, and place it in a
column of a worksheet:

PasteRange = RecSet.GetRows
ReDim ReturnVals(0 To (UBound(PasteRange, 2) - 1))
For Index = 0 To UBound(PasteRange, 2) - 1
ReturnVals(Index) = PasteRange(2, Index)
Next
Range("AP7:AP469").Value =
Application.WorksheetFunction.Transpose(ReturnVals)

Stepping through in the VB editor shows that the 1-D array indeed
contains the correct information. The last line, however, always
errors out with "Type Mismatch".

Other info: PasteRange and ReturnVals are both Dim'd variant, and the
ODBC query always returns 462 rows. I'm only using the UBound stuff
so there's fewer lines of code to change if the database changes.

Any ideas?

-John
 
John,

I do not know why you are getting the error in the Transpose line.
However, you do not need to use Transpose as you can write the PasteRange
values into ReturnVals and eliminate the need to transpose it.
By the way Range("AP7:AP469") has 463 rows not 462.
Try something like...
'----------------------------
Sub TestArray()
Dim PasteRange As Variant
Dim ReturnVals As Variant
Dim Index As Long

PasteRange = Array(11, 2, 3, 4, 5, 6, 7, 88)
ReDim ReturnVals(0 To UBound(PasteRange), 1 To 1)

For Index = 0 To UBound(PasteRange)
ReturnVals(Index, 1) = PasteRange(Index)
Next

Range("AP7:AP14").Value = ReturnVals
Erase ReturnVals
End Sub
'-------------------------------------------

Regards,
Jim Cone
San Francisco, CA
 
Jim Cone said:
John,

I do not know why you are getting the error in the Transpose line.
However, you do not need to use Transpose as you can write the PasteRange
values into ReturnVals and eliminate the need to transpose it.
By the way Range("AP7:AP469") has 463 rows not 462.
Try something like...
Click to expand...

Thanks Jim! Copying the needed data into a 2-d array worked out great!

-John
 
Back
Top