+ Reply to Thread
Results 1 to 7 of 7

Join elements in an array and output to a single cell

Hybrid View

  1. #1
    Forum Contributor
    Join Date
    10-22-2012
    Location
    USA
    MS-Off Ver
    Excel 2010
    Posts
    114

    Join elements in an array and output to a single cell

    Hi all,

    Thanks in advance for helping. Hope the subject line above is clear enough.

    This line of code error out in the code:

    Cells(r,"D") = Join(NewText, "/")

    Run-time error '5':
    Invalid procedure call or argument

    
    Sub test()
    
    Dim NewText() As Variant
    
    OldText = Range("C4:C8")
    r = 4
      For Each c In OldText
            cnt = 0
        For i = 1 To Len(c)
            
            If Asc(Mid(c, i, 1)) > 48 And Asc(Mid(c, i, 1)) <= 57 Then
                cnt = cnt + 1
                ReDim Preserve NewText(1 To 1, 1 To cnt)
                NewText(1, cnt) = Mid(c, i, 1)
            End If
      Next
        
         Cells(r, "D") = Join(NewText, "/")
        r = r + 1
    Next
    
    End Sub

  2. #2
    Forum Guru
    Join Date
    04-13-2005
    Location
    North America
    MS-Off Ver
    2002/XP, 2007, 2024
    Posts
    16,380

    Re: Join elements in an array and output to a single cell

    I came across this once before. NewText is Redimmed as a 2D array (even though the first dimension is 1 to 1, it is still a 2D array), but the Join() function can only operate on 1D arrays. Why are you using NewText as a 2D array? If there is no specific reason for NewText to be 2D, the solution is to Redim NewText as a 1D array
    ReDim Preserve NewText(1 to cnt)
    NewText(cnt)=Mid(c,i,1)
    Quote Originally Posted by shg
    Mathematics is the native language of the natural world. Just trying to become literate.

  3. #3
    Forum Contributor
    Join Date
    10-22-2012
    Location
    USA
    MS-Off Ver
    Excel 2010
    Posts
    114

    Re: Join elements in an array and output to a single cell

    MrShorty, I suspected it was the problem but wan't sure. Thank you for helping. It work great now.

  4. #4
    Forum Expert
    Join Date
    02-11-2014
    Location
    New York
    MS-Off Ver
    Excel 365 (Windows)
    Posts
    6,274

    Re: Join elements in an array and output to a single cell

    In addition to the array needing to be 1D instead of 2D, since you are populating the array 1 element at a time, you could skip that and just build the output string:

    Sub test()
        Dim NewText As String
        Dim oldtext As Range
        Dim c As Range
        Dim cnt As Long
        Dim i As Long
        
        Set oldtext = Range("C4:C8")
        
        For Each c In oldtext
            NewText = ""
            For i = 1 To Len(c)
                If Asc(Mid(c, i, 1)) > 48 And Asc(Mid(c, i, 1)) <= 57 Then
                    If NewText = "" Then
                        NewText = Mid(c, i, 1)
                    Else
                        NewText = NewText & "/" & Mid(c, i, 1)
                    End If
                End If
            Next i
            
            c(1, 2).Value = NewText
             
        Next c
        
    End Sub
    Bernie Deitrick
    Excel MVP 2000-2010

  5. #5
    Forum Contributor
    Join Date
    10-22-2012
    Location
    USA
    MS-Off Ver
    Excel 2010
    Posts
    114

    Re: Join elements in an array and output to a single cell

    Bernie Deitrick - Thank you for helping. A great different approach.

  6. #6
    Forum Expert
    Join Date
    02-11-2014
    Location
    New York
    MS-Off Ver
    Excel 365 (Windows)
    Posts
    6,274

    Re: Join elements in an array and output to a single cell

    I should also have pointed out a possible issue:

    If you have 2 or 3 numbers in a cell, the output could be converted to a date - You might want to format the output cells as text prior to running the code or in code prior to entering the values to prevent that.

  7. #7
    Forum Contributor
    Join Date
    10-22-2012
    Location
    USA
    MS-Off Ver
    Excel 2010
    Posts
    114

    Re: Join elements in an array and output to a single cell

    Bernie Deitrick - Great point! I will use a semicolon ";" Thanks

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. Replies: 14
    Last Post: 11-26-2014, 05:26 PM
  2. Join Array with Text elements
    By cmore in forum Excel Programming / VBA / Macros
    Replies: 6
    Last Post: 07-03-2014, 12:23 AM
  3. [SOLVED] Removing specific elements from an Array based on matching elements in a different Array
    By jamesdeuce in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 01-24-2014, 10:35 PM
  4. multiple string dates in cell, to array, and manipulate single elements
    By willara23 in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 09-18-2013, 07:48 PM
  5. [SOLVED] Paste variable output (SQL Output) into a single cell.
    By fblaze in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 02-28-2013, 06:24 AM
  6. [SOLVED] Populate Listbox with all rows of a dynamic array where elements of a single array match.
    By Tayque_J_Holmes in forum Excel Programming / VBA / Macros
    Replies: 21
    Last Post: 08-07-2012, 04:54 AM
  7. Reorder elements a cell using arrays, split & join
    By k8_dog in forum Excel Programming / VBA / Macros
    Replies: 8
    Last Post: 01-12-2011, 01:58 PM

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