Array

[Bill]

Hello,
I want to transpose a range in an excel data and store the values in an
array. The code I have is:

Sub TransposeThat()
Set DB = Selection
nrows = DB.Rows.Count
ncols = DB.Columns.Count
For i = 1 To nrows
For j = 1 To ncols
ReDim Preserve TArray(1 To j, 1 To i)
TArray(j, i) = DB(i, j)
Next j
Next i
End Sub

DB is the range on the worksheet. I get the message: subscript out of range
applying to the redim preserve statement.

Why am I getting that error?

Thanks,

bill

 

[Tom Ogilvy]

Read the help file on Redim ' ng an array. It says you can only redim
preserve the last dimension. But you don't need to do it anyway, so use
this:


Sub TransposeThat()
Dim TArray() As Variant
Dim DB as Range, i as Long, j as Long
Dim nRows as Long, nCols as Long
Set DB = Selection
nrows = DB.Rows.Count
ncols = DB.Columns.Count
redim TArray(1 to ncols, 1 to nrows)
For i = 1 To nrows
For j = 1 To ncols
TArray(j, i) = DB(i, j)
Next j
Next i
End Sub