Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
One-D Array to Column Problem
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
One-D Array to Column Problem
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 "John" wrote in message om... 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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
One-D Array to Column Problem
"Jim Cone" wrote in message ...
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... Thanks Jim! Copying the needed data into a 2-d array worked out great! -John |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
3D Array Problem | Excel Worksheet Functions | |||
COLUMN() problem with array formula | Excel Worksheet Functions | |||
How to manipulate an 2D Array into a Column Array? | Excel Discussion (Misc queries) | |||
Array problem - TIA | Excel Worksheet Functions | |||
Array Problem | Excel Discussion (Misc queries) |