+ Reply to Thread
Results 1 to 5 of 5

Pasting an array into a range

  1. #1
    Tony
    Guest

    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. #2
    Myrna Larson
    Guest

    Re: 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" <[email protected]> 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. #3
    Guest

    Re: 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"

    <[email protected]> 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. #4
    Peter T
    Guest

    Re: 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

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

    > <[email protected]> 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. #5
    Alan Beban
    Guest

    Re: Pasting an array into a range

    [email protected] wrote:
    > 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
    >


    There are some limitations on the built-in TRANSPOSE function. You are
    running into the one that limits, in xl2000 and prior, the operation of
    the built-in function to 5461 elements when transferring between
    worksheets and VBA arrays. Another limitation, which I assume remains
    beyond xl2000 though I am not positive, is that the type of the array is
    not preserved by the built-in function when transferring from VBA array
    to VBA array. The following is a more general purpose Transpose function
    without the limitations; watch for word wrap.

    Function ArrayTranspose(InputArray)
    'This function returns the transpose of
    'the input array or range; it is designed
    'to avoid the limitation on the number of
    'array elements and type of array that the
    'worksheet TRANSPOSE Function has.

    'Declare the variables
    Dim outputArrayTranspose As Variant, arr As Variant, p As Integer
    Dim i As Long, j As Long

    'Check to confirm that the input array
    'is an array or multicell range
    If IsArray(InputArray) Then

    'If so, convert an input range to a
    'true array
    arr = InputArray

    'Load the number of dimensions of
    'the input array to a variable
    On Error Resume Next

    'Loop until an error occurs
    i = 1
    Do
    z = UBound(arr, i)
    i = i + 1
    Loop While Err = 0

    'Reset the error value for use with other procedures
    Err = 0

    'Return the number of dimensions
    p = i - 2
    End If

    If Not IsArray(InputArray) Or p > 2 Then
    Msg = "#ERROR! The function accepts only multi-cell ranges and
    1D or 2D arrays."
    If TypeOf Application.Caller Is Range Then
    ArrayTranspose = Msg
    Else
    MsgBox Msg, 16
    End If
    Exit Function
    End If

    'Load the output array from a one-
    'dimensional input array
    If p = 1 Then

    Select Case TypeName(arr)
    Case "Object()"
    ReDim outputArrayTranspose(LBound(arr) To UBound(arr),
    LBound(arr) To LBound(arr)) As Object
    For i = LBound(outputArrayTranspose) To
    UBound(outputArrayTranspose)
    Set outputArrayTranspose(i,
    LBound(outputArrayTranspose)) = arr(i)
    Next
    Case "Boolean()"
    ReDim outputArrayTranspose(LBound(arr) To UBound(arr),
    LBound(arr) To LBound(arr)) As Boolean
    Case "Byte()"
    ReDim outputArrayTranspose(LBound(arr) To UBound(arr),
    LBound(arr) To LBound(arr)) As Byte
    Case "Currency()"
    ReDim outputArrayTranspose(LBound(arr) To UBound(arr),
    LBound(arr) To LBound(arr)) As Currency
    Case "Date()"
    ReDim outputArrayTranspose(LBound(arr) To UBound(arr),
    LBound(arr) To LBound(arr)) As Date
    Case "Double()"
    ReDim outputArrayTranspose(LBound(arr) To UBound(arr),
    LBound(arr) To LBound(arr)) As Double
    Case "Integer()"
    ReDim outputArrayTranspose(LBound(arr) To UBound(arr),
    LBound(arr) To LBound(arr)) As Integer
    Case "Long()"
    ReDim outputArrayTranspose(LBound(arr) To UBound(arr),
    LBound(arr) To LBound(arr)) As Long
    Case "Single()"
    ReDim outputArrayTranspose(LBound(arr) To UBound(arr),
    LBound(arr) To LBound(arr)) As Single
    Case "String()"
    ReDim outputArrayTranspose(LBound(arr, 2) To
    UBound(arr, 2), LBound(arr, 1) To UBound(arr, 1)) As String
    Case "Variant()"
    ReDim outputArrayTranspose(LBound(arr) To UBound(arr),
    LBound(arr) To LBound(arr)) As Variant
    Case Else
    Msg = "#ERROR! Only built-in types of arrays are
    supported."
    If TypeOf Application.Caller Is Range Then
    ArrayTranspose = Msg
    Else
    MsgBox Msg, 16
    End If
    Exit Function
    End Select
    If TypeName(arr) <> "Object()" Then
    For i = LBound(outputArrayTranspose) To
    UBound(outputArrayTranspose)
    outputArrayTranspose(i, LBound(outputArrayTranspose)) =
    arr(i)
    Next
    End If

    'Or load the output array from a two-
    'dimensional input array or range
    ElseIf p = 2 Then
    Select Case TypeName(arr)
    Case "Object()"
    ReDim outputArrayTranspose(LBound(arr, 2) To
    UBound(arr, 2), _
    LBound(arr) To UBound(arr)) As Object
    For i = LBound(outputArrayTranspose) To _
    UBound(outputArrayTranspose)
    For j = LBound(outputArrayTranspose, 2) To _
    UBound(outputArrayTranspose, 2)
    Set outputArrayTranspose(i, j) = arr(j, i)
    Next
    Next
    Case "Boolean()"
    ReDim outputArrayTranspose(LBound(arr, 2) To
    UBound(arr, 2), _
    LBound(arr) To UBound(arr)) As Boolean
    Case "Byte()"
    ReDim outputArrayTranspose(LBound(arr, 2) To
    UBound(arr, 2), _
    LBound(arr) To UBound(arr)) As Byte
    Case "Currency()"
    ReDim outputArrayTranspose(LBound(arr, 2) To
    UBound(arr, 2), _
    LBound(arr) To UBound(arr)) As Currency
    Case "Date()"
    ReDim outputArrayTranspose(LBound(arr, 2) To
    UBound(arr, 2), _
    LBound(arr) To UBound(arr)) As Date
    Case "Double()"
    ReDim outputArrayTranspose(LBound(arr, 2) To
    UBound(arr, 2), _
    LBound(arr) To UBound(arr)) As Double
    Case "Integer()"
    ReDim outputArrayTranspose(LBound(arr, 2) To
    UBound(arr, 2), _
    LBound(arr) To UBound(arr)) As Integer
    Case "Long()"
    ReDim outputArrayTranspose(LBound(arr, 2) To
    UBound(arr, 2), _
    LBound(arr) To UBound(arr)) As Long
    Case "Single()"
    ReDim outputArrayTranspose(LBound(arr, 2) To
    UBound(arr, 2), _
    LBound(arr) To UBound(arr)) As Single
    Case "String()"
    ReDim outputArrayTranspose(LBound(arr, 2) To
    UBound(arr, 2), _
    LBound(arr) To UBound(arr)) As String
    Case "Variant()"
    ReDim outputArrayTranspose(LBound(arr, 2) To
    UBound(arr, 2), _
    LBound(arr) To UBound(arr)) As Variant
    Case Else
    Msg = "#ERROR! Only built-in types of arrays are
    supported."
    If TypeOf Application.Caller Is Range Then
    ArrayTranspose = Msg
    Else
    MsgBox Msg, 16
    End If
    Exit Function
    End Select
    If TypeName(arr) <> "Object()" Then
    For i = LBound(outputArrayTranspose) To _
    UBound(outputArrayTranspose)
    For j = LBound(outputArrayTranspose, 2) To _
    UBound(outputArrayTranspose, 2)
    outputArrayTranspose(i, j) = arr(j, i)
    Next
    Next
    End If
    End If

    'Return the transposed array
    ArrayTranspose = outputArrayTranspose
    End Function

    Alan Beban

    >>-----Original Message-----
    >>Your code works as posted for me in Excel 2002.
    >>
    >>On Thu, 6 Jan 2005 13:18:56 -0800, "Tony"

    >
    > <[email protected]> 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

    >>
    >>.
    >>


+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1