Pasting an array into a range
 

Hi all:

I get a runtime 'type mismatch' on the last statement
(i.e., r.Value = ). Any thoughts?

Sub test()
Dim r As Range
Set r = ActiveSheet.Range("A1:A50000")

Dim arr() As Long
Dim i As Long
ReDim arr(1 To 50000)
For i = 1 To 50000
arr(i) = i
Next i

r.Value = Application.WorksheetFunction.Transpose(arr)

End Sub

Thanks, Tony

Pasting an array into a range
 

Your code works as posted for me in Excel 2002.

On Thu, 6 Jan 2005 13:18:56 -0800, "Tony" wrote:

Hi all:

I get a runtime 'type mismatch' on the last statement
(i.e., r.Value = ). Any thoughts?

Sub test()
Dim r As Range
Set r = ActiveSheet.Range("A1:A50000")

Dim arr() As Long
Dim i As Long
ReDim arr(1 To 50000)
For i = 1 To 50000
arr(i) = i
Next i

r.Value = Application.WorksheetFunction.Transpose(arr)

End Sub

Thanks, Tony

Pasting an array into a range
 

Maybe that's it - I'm working in xl97. I forgot to
mention that. Is there a fix to make it work in 97?

Tony
-----Original Message-----
Your code works as posted for me in Excel 2002.

On Thu, 6 Jan 2005 13:18:56 -0800, "Tony"
wrote:

Hi all:

I get a runtime 'type mismatch' on the last statement
(i.e., r.Value = ). Any thoughts?

Sub test()
Dim r As Range
Set r = ActiveSheet.Range("A1:A50000")

Dim arr() As Long
Dim i As Long
ReDim arr(1 To 50000)
For i = 1 To 50000
arr(i) = i
Next i

r.Value = Application.WorksheetFunction.Transpose(arr)

End Sub

Thanks, Tony

.

Pasting an array into a range
 

Hi Tony,

I think you've hit the 5461 elements in an array limit, in this case with
the transpose function. To avoid needing to transpose the array work with a
vertical array. In your particular example this would also be a more
efficient method for later versions which are unaffected by the 5461 limit.

Sub test2()
Dim r As Range
Set r = ActiveSheet.Range("A1:A50000")

Dim arr() As Long
Dim i As Long
ReDim arr(1 To 50000, 1 To 1)
For i = 1 To 50000
arr(i, 1) = i
Next i

r.Value = arr

End Sub

Regards,
Peter T

wrote in message
...
Maybe that's it - I'm working in xl97. I forgot to
mention that. Is there a fix to make it work in 97?

Tony
-----Original Message-----
Your code works as posted for me in Excel 2002.

On Thu, 6 Jan 2005 13:18:56 -0800, "Tony"
wrote:

Hi all:

I get a runtime 'type mismatch' on the last statement
(i.e., r.Value = ). Any thoughts?

Sub test()
Dim r As Range
Set r = ActiveSheet.Range("A1:A50000")

Dim arr() As Long
Dim i As Long
ReDim arr(1 To 50000)
For i = 1 To 50000
arr(i) = i
Next i

r.Value = Application.WorksheetFunction.Transpose(arr)

End Sub

Thanks, Tony

.

Pasting an array into a range