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


--
Regards,
Tom Ogilvy


"Bill" <[email protected]> wrote in message
news:[email protected]...
> 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
>
>