Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 . |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 . |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Pasting an array into a range
|
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Copying and pasting from a range | Excel Worksheet Functions | |||
VBA pasting array into cells | Excel Discussion (Misc queries) | |||
Pasting a portion of an array | Excel Programming | |||
Pasting a Range to Word | Excel Programming | |||
Help - Selecting and pasting into range | Excel Programming |